2

This question already has an answer here:

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 |

marked as duplicate by Paul White sql-server May 10 '16 at 8:10

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.