Erdem Erdem - 2 months ago 16
SQL Question

Transpose some columns to row

I know that same kind of questions has been asked before. However I didn't succeed to do what I need to do. Therefore I'm asking you.

I have a table with client_ID and some probabilities of purchasing different product category corresponding to each client.

Client_ID | Prob_CategoryA | Prob_CategoryB | Prob_CategoryC

1 0.2 0.3 0.2
2 0.4 0.6 0.7
3 0.3 0.7 0.4


Now what I would like to do is transform the above table into this.

Client_ID | Category Name | Probability

1 A 0.2
1 B 0.3
1 C 0.2
2 A 0.4
2 B 0.6
2 C 0.7
3 A 0.3
3 B 0.7
3 C 0.4


Thank you very much

Answer

Simple UNPIVOT:

SELECT Client_Id, SUBSTRING(Cat, 14, 1) [Category Name], Probability
FROM Src
UNPIVOT (Probability FOR Cat IN (Prob_CategoryA, Prob_CategoryB, Prob_CategoryC)) UP

Result

Client_Id   Category Name Probability
----------- ------------- -----------
1           A             0.2
1           B             0.3
1           C             0.2
2           A             0.4
2           B             0.6
2           C             0.7
3           A             0.3
3           B             0.7
3           C             0.4
Comments