Transpose some columns to row

Question

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 1

Use UNPIVOT:

SELECT Client_ID, Cats, Probability
FROM
(SELECT Client_ID, Prob_CategoryA, Prob_CategoryB, Prob_CategoryC
 FROM yourTable) t
UNPIVOT
(Probability FOR Cats IN (Prob_CategoryA, Prob_CategoryB, Prob_CategoryC)
) AS c

Answer 2

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