CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivotunpivot
AA.SC
AA.SC

Reputation: 377

Convert more than one Columns into Rows

I have a table with columns and value like 

ID  Values  FirstCol    2ndCol  3rdCol  4thCol  5thCol
1   1stValue    5466    34556   53536   54646   566
1   2ndValue    3544    957     667     1050    35363
1   3rdValue    1040    1041    4647    6477    1045
1   4thValue    1048    3546    1095    1151    65757
2   1stValue    845     5466    86578   885     859
2   2ndValue    35646   996     1300    7101    456467
2   3rdValue    102     46478   565     657     107
2   4thValue    5509    55110   1411    1152    1144
3   1stValue    845     854     847     884     675
3   2ndValue    984     994     4647    1041    1503
3   3rdValue    1602    1034    1034    1055    466
3   4thValue    1069    1610    6111    1124    1144

Now I want a result set in below form, is this possible with Pivot or Case statment?

ID  Cols        1stValue    2ndValue    3rdValue    4thValue
1   FirstCol    5466        3544        1040        1048
1   2ndCol      34556       957         1041        3546
1   3rdCol      53536       667         4647        1095
1   4thCol      54646       1050        6477        1151
1   5thCol      566         35363       1045        65757
2   FirstCol    845         35646       102         5509
2   2ndCol      5466        996         46478       55110
2   3rdCol      86578       1300        565         1411
2   4thCol      885         7101        657         1152
2   5thCol      859         456467      107         1144
3   FirstCol    845         984         1602        1069
3   2ndCol      854         994         1034        1610
3   3rdCol      847         4647        1034        6111
3   4thCol      884         1041        1055        1124
3   5thCol      675         1503        466         1144

Upvotes: 0

Views: 86

Answers (1)

Endrju
Endrju

Reputation: 2436

Assuming the table name is t1 this should do the trick:

SELECT * FROM t1
UNPIVOT (val FOR name IN ([FirstCol], [2ndCol], [3rdCol], [4thCol], [5thCol])) unpiv
PIVOT (SUM(val) FOR [Values] IN ([1stValue], [2ndValue], [3rdValue], [4thValue])) piv

There's sorting issue, it'd be good to rename FirstCol to 1stCol, then ORDER BY ID, name would put it in required order.

Upvotes: 1

Related Questions