CODEWITHSUNDEEP
sqlsql-serversql-server-2012pivot
user1492051
user1492051

Reputation: 906

Creating columns from rows

I have this table

ID      colname     F1        F2    F3       F4      
1       P1          1         2     3        4
1       P2          5         6     7        8
1       P3          9         10    11       12
1       P4          13        14    15       16
2       P1          17        18    19       20
2       P2          21        22    23       24
2       P3          25        26    27       28
2       P4          29        30    31       32

I am trying to produce this result Pn value corresponds to Fn

 ID     P1        P2     P3       P4 
 1       1        6      11       16
 2       17       22     27       32

can you please give me some hints and keywords on how it could be done with in SQL Server?

i've been playing with Pivot but is it the way to go?

Upvotes: 2

Views: 54

Answers (2)

Anon
Anon

Reputation: 10908

If you want a solution that uses PIVOT:

SELECT *
FROM (
  SELECT ID,colname,value
  FROM MyTable
  UNPIVOT (value FOR col in ([F1],[F2],[F3],[F4])) a
  WHERE REPLACE(col,'F','P') = colname
) b
PIVOT (MAX(value) FOR colname in ([P1],[P2],[P3],[P4])) c

The UNPIVOT followed PIVOT method is extremely versatile for transforms, but it's usually easier and more readable to do it manually as in Roman's example.

Upvotes: 1

roman
roman

Reputation: 117380

Well you can do something like this:

select
     id,
     max(case when colname = 'P1' then F1 end) as P1,
     max(case when colname = 'P2' then F2 end) as P2,
     max(case when colname = 'P3' then F3 end) as P3,
     max(case when colname = 'P4' then F4 end) as P4
from Table1
group by id

sql fiddle demo

Upvotes: 3

Related Questions