none
JOIN ROWS

    Question

  • Hi,

    I need help on the below please

    DECLARE @t TABLE
    (
    ID int,
    Color1 int,
    Color2 int,
    Color3 int
    )

    INSERT INTO @t
    VALUES (1, NULL, NULL, 5)
    , (1, NULL, 10, NULL)
    ,(1, 0, NULL, NULL)
    ,(2, NULL, NULL, 54)
    , (2, NULL, 140, NULL)
    ,(2, 0, NULL, NULL)

    SELECT * FROM @t

    Returns the below

    ID  Color1 Color2 Color3
    1   NULL    NULL    5
    1   NULL    10       NULL
    1   0          NULL   NULL
    2   NULL    NULL   54
    2   NULL    140     NULL
    2   0          NULL   NULL

    Each ID will always have 3 rows, I would like to merge the cells for each ID into one row ie

    ID  Color1 Color2 Color3
    1    0        10       5
    2   0         140     54

    Please help?

    Monday, July 22, 2013 12:05 AM

Answers

  • Can be done like this

    SELECT
    	ID, 
    	MAX(Color1) as Color1, 
    	MAX(Color2) as Color2, 
    	MAX(Color3) as Color3
    FROM @t
    GROUP BY ID

    • Proposed as answer by Naomi NModerator Monday, July 22, 2013 12:30 AM
    • Marked as answer by Milli_22 Monday, July 22, 2013 12:31 AM
    Monday, July 22, 2013 12:16 AM

All replies