PIVOT for unlabeled data

user_14415669 0
Viewed 26

I'm fairly new to TSQL and have an issue I hope you can help with!

My data is organized as follows:

+-------+-------+
| Name  | Phone |
+-------+-------+
| John  | xxx   |
+-------+-------+
| John  | yyy   |
+-------+-------+
| Sally | www   |
+-------+-------+
| Sue   | qqq   |
+-------+-------+
| John  | aaa   |
+-------+-------+
| Sally | ppp   |
+-------+-------+

I would like it to come out after the transform as:

+-------+--------+--------+--------+
| Name  | Phone1 | Phone2 | Phone3 |
+-------+--------+--------+--------+
| John  | xxx    | yyy    | aaa    |
+-------+--------+--------+--------+
| Sally | www    | ppp    |        |
+-------+--------+--------+--------+
| Sue   | qqq    |        |        |
+-------+--------+--------+--------+

The issue I have been running into is, one 'Name' has an undefined number of 'Phones'. Some may have one while others could have 10.

Thanks guys, appreciate any help you can give me.

William.

tsql sql-server sql
1 Answers

A simple pivot in concert with row_number() should do the trick

Just add Phone5 ... Phone10

Example

Select *
 From  ( 
        Select Name
              ,Phone
              ,Col = concat('Phone',row_number() over (partition by name order by (select null))) 
         from YourTable
       ) src
 Pivot ( max(Phone) for Col in ([Phone1],[Phone2],[Phone3],[Phone4]) ) pvt

Returns

Name    Phone1  Phone2  Phone3  Phone4
John    xxx     yyy     aaa     NULL
Sally   ppp     www     NULL    NULL
Sue     qqq     NULL    NULL    NULL
user_15700000
Related