Erdem - 1 year ago 71

SQL 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 Source

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
```