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)

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

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

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results