Convert multiple rows into column base on another column value [duplicate]

Question

This question already has an answer here:

Need help with SQL Server PIVOT (1 answer)

Closed 9 years ago.

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 |

Answer 1

Table and sample data

CREATE TABLE dbo.Data
(
 [No] integer NOT NULL,
 Name varchar(4) NOT NULL,
 Relation varchar(2) NOT NULL
);
INSERT dbo.Data
 ([No], Name, Relation)
VALUES
 (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');

Query

The basic technique is shown on the related question:

Need help with SQL Server PIVOT

The only extra trick is to get the numbering right:

SELECT
 P.[No], P.P,
 P.JB1, P.JB2, P.JB3, P.JB4, P.JB5,
 P.GO1, P.GO2, P.GO3, P.GO4, P.GO5, P.GO6
FROM 
(
 SELECT
 D.[No],
 D.Name,
 RelationSeq = 
 CASE
 -- Add a sequence number for rows with Relation JB or GO
 WHEN D.Relation IN ('JB', 'GO')
 THEN D.Relation + 
 CONVERT
 (
 char(1),
 ROW_NUMBER() OVER (
 PARTITION BY D.[No], D.Relation
 ORDER BY 
 CONVERT(integer, SUBSTRING(D.Name, 3, 2)))
 )
 -- Otherwise just use the Relation
 ELSE CONVERT(varchar(3), D.Relation)
 END
 FROM dbo.Data AS D
) AS S
PIVOT 
(
 -- Standard pivot rows to columns
 MAX(S.Name) 
 FOR RelationSeq IN 
 (P, JB1, JB2, JB3, JB4, JB5, GO1, GO2, GO3, GO4, GO5, GO6)
) AS P;

Demo

Try it on Stack Exchange Data Explorer

Result

Result

If you think this query is more difficult than it should be - you're right. Storing the original data in a more normalized structure would have made it considerably simpler. Poor design often leads to difficult queries.