PIVOT two columns and keep others as they are

Question

I want to turn some of the rows into columns while keeping other rows as they are.

ID      name     value     RefId
1       Fname    John      32145
2       LName    Smith     32145
3       Fname    Peter     34589
4       LName    Mahlang   34589

Now what I want to achieve is to turn the Fname and Lname rows into columns with their matching value field. ID column doesn't really matter, I don't need it.

Desired Output

       Fname     Lname     RefId
       John      Smith     32145
       Peter     Mahlang   34589

Any help

score 2 · Accepted Answer · answered May 17 '17 at 16:42

Using conditional aggregation:

select 
    Fname = max(case when name = 'Fname' then value end)
  , Lname = max(case when name = 'Lname' then value end)
  , RefId
from t
group by RefId

rextester demo: http://rextester.com/MRMY11592

returns:

+---------+---------+-------+
|  Fname  |  Lname  | RefId |
+---------+---------+-------+
| John    | Smith   | 32145 |
| Peter   | Mahlang | 34589 |
+---------+---------+-------+

Or using pivot()

select 
    Fname
  , Lname
  , RefId
from (select name, value, refid from t) s
pivot(max(value) for name in ([Fname],[Lname]))p

Thanks SqlZim. It works perfectly. Much appreciate your help. — BV_Data, May 18 '17 at 07:07

PIVOT two columns and keep others as they are

1 Answers1

Linked