Pivoting with duplicate Values

  • Hi,

    I have a table structure similar to the following code.

    DECLARE @PatDev TABLE (

    PID INT,

    VisitType CHAR(2),

    Rn tinyint)

    INSERT @PatDev

    VALUES

    (1001, 'V1', 1)

    ,(1001, 'V2', 1)

    ,(1001, 'V3', 2)

    ,(1001, 'V4', 3)

    ,(1001, 'V5', 4)

    ,(1002, 'V1', 1)

    ,(1002, 'V1', 2)

    ,(1002, 'V2', 2)

    ,(1002, 'V1', 3)

    ,(1002, 'V1', 4)

    ,(1003, 'V2', 1)

    ,(1003, 'V1', 2)

    ,(1003, 'V4', 3)

    ,(1003, 'V3', 3)

    ,(1003, 'V2', 4)

    ,(1004, 'V2', 1)

    ,(1004, 'V3', 2)

    ,(1004, 'V5', 3)

    ,(1004, 'V2', 4)

    ,(1005, 'V2', 1)

    ,(1005, 'V2', 2)

    ,(1005, 'V2', 3)

    ,(1005, 'V1', 4)

    ,(1005, 'V5', 4)

    ,(1005, 'V3', 4)

    SELECT *

    FROM @PatDev

    Expected Output:

    PIDCol1Col2Col3Col4

    1001V1V3V4V5

    1001V2V3V4V5

    1002V1V1V1V1

    1002V1V2V1V1

    1003V2V1V4V2

    1003V2V1V3V2

    1004V2V3V5V2

    1005V2V2V2V1

    1005V2V2V2V5

    1005V2V2V2V3

    Expected Output in a table

    DECLARE @OutPut as table

    (

    PID int

    , Visit1 CHAR(2)

    , Visit2 CHAR(2)

    , Visit3 CHAR(2)

    , Visit4 CHAR(2)

    )

    INSERT INTO @OutPut

    VALUES

    (1001,'V1','V3','V4','V5')

    ,(1001,'V2','V3','V4','V5')

    ,(1002,'V1','V1','V1','V1')

    ,(1002,'V1','V2','V1','V1')

    ,(1003,'V2','V1','V4','V2')

    ,(1003,'V2','V1','V3','V2')

    ,(1004,'V2','V3','V5','V2')

    ,(1005,'V2','V2','V2','V1')

    ,(1005,'V2','V2','V2','V5')

    ,(1005,'V2','V2','V2','V3')

    SELECT *

    FROM @OutPut

    Thanks in advance...

  • WITH a (PID,VisitType,Rn,RowNo)

    AS (

    SELECT PID,VisitType,Rn

    ,ROW_NUMBER() OVER (PARTITION BY PID,Rn ORDER BY VisitType ASC)

    FROM @PatDev

    ),

    b (PID,MaxRowNo)

    AS (

    SELECT PID,MAX(RowNo)

    FROM a

    GROUP BY PID

    )

    SELECT b.PID

    ,MAX(CASE WHEN c.C = 1 THEN ISNULL(a2.VisitType,a.VisitType) END) AS [Col1]

    ,MAX(CASE WHEN c.C = 2 THEN ISNULL(a2.VisitType,a.VisitType) END) AS [Col2]

    ,MAX(CASE WHEN c.C = 3 THEN ISNULL(a2.VisitType,a.VisitType) END) AS [Col3]

    ,MAX(CASE WHEN c.C = 4 THEN ISNULL(a2.VisitType,a.VisitType) END) AS [Col4]

    FROM b

    JOIN (SELECT R FROM (VALUES (1),(2),(3),(4)) n (R)) r

    ON r.R BETWEEN 1 AND b.MaxRowNo

    CROSS JOIN (SELECT C FROM (VALUES (1),(2),(3),(4)) n (C)) c

    JOIN a ON a.PID = b.PID AND a.RowNo = 1 AND a.Rn = c.C

    LEFT JOIN a a2 ON a2.PID = b.PID AND a2.RowNo = r.R AND a2.Rn = c.C

    GROUP BY b.PID,r.R

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Thanks a Lot for your solution.

    That's the brilliant use of T-SQL features

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply