This question already has an answer here:
- Need help with SQL Server PIVOT 1 answer
I have search through the answer but could not get what i'd like to have. my table is as below.
No | Name | Relation 1 | A | P 1 | B | GO 1 | C | JB 2 | AH | P 3 | TY | P 3 | DO | JB 3 | DO1 | JB 3 | DO2 | JB 3 | DO3 | JB 3 | DO4 | JB 3 | DO5 | GO 3 | DO6 | GO 3 | DO7 | GO 3 | DO8 | GO 3 | DO9 | GO 3 | DO10 | GO 4 | PA | P 4 | PA2 | GO 4 | PA3 | GO 4 | PA4 | GO 4 | PA5 | GO 4 | PA6 | GO 4 | PA7 | GO
Each No will have in Relation only one 'P', max 5 'JB' and max 6 'GO'.
I'd like to get below result.
No | P | JB1 | JB2 | JB3 | JB4 | JB5 | GO1 | GO2 |GO3 |GO4 |GO5 |GO6 | 1 | A | C | NULL| NULL| NULL| NULL| B | NULL|NULL|NULL|NULL|NULL| 2 | AH | NULL | NULL| NULL| NULL| NULL| NULL| NULL|NULL|NULL|NULL|NULL| 3 | TY | DO | DO1 | DO2 | DO3 | DO4 | DO5 | DO6 |DO7 |DO8 |DO9 |DO10| 4 | PA | NULL |NULL |NULL |NULL |NULL | PA2 | PA3 |PA4 |PA5 |PA6 |PA7 |