Convert multiple rows into column base on another column value 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 |
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



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.