Convert multiple rows into column base on another column value

0

Please login or register to vote for this query.

(click on this box to dismiss)

Windows Phone Meta

Q&A about the site for enthusiasts and power users of Windows Phone OS

CREATE TABLE #Data
(
    [No] integer NOT NULL,
    Name varchar(4) NOT NULL,
    Relation varchar(2) NOT NULL
);

INSERT #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');

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 #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;

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results