Rolling Up Multiple Rows Into Single Row With Same Number Of Columns and Different Values

user_15937423 0
Viewed 635

I have a ID column with different values in it for a particular attribute. I wish to convert the 4 rows into 1 row. There are multiple records

UNIQID    | NAME | AGE | SOURCE 
123       | John | 25  | StoreA
928       | John | 25  | StoreB
456       | John | 25  | StoreC
528       | John | 25  | StoreD

I need the output as below -

UNIQID_A    | UNIQID_B | UNIQID_C | UNIQID_D | NAME | AGE 
123         | 928      | 456      | 528      | John | 25

I manged to reach the below result by using the sql query but I am not sure how to roll up these values into 1

SELECT  CASE WHEN Source = 'StoreA' THEN UNIQID END AS UNIQID_A,
        CASE WHEN Source = 'StoreB' THEN UNIQID END AS UNIQID_B,
        CASE WHEN Source = 'StoreC' THEN UNIQID END AS UNIQID_C,
        CASE WHEN Source = 'StoreD' THEN UNIQID END AS UNIQID_D ,
        ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY AGE) AS ROWNO,
        * 
FROM Table

Output of the above query as

UNIQID_A     | UNIQID_B | UNIQID_C | UNIQID_D | NAME | AGE | ROWNO
123          | NULL     | NULL     | NULL     | John | 25  | 1
NULL         | 928      | NULL     | NULL     | John | 25  | 2
NULL         | NULL     | 456      | NULL     | John | 25  | 3
NULL         | NULL     | NULL     | 528      | John | 25  | 4

Appreciate your help.

sql-server-2012 tsql sql-server sql
2 Answers

You can use max on your conditional statements to aggregate into a single row, and group by the remaining columns

SELECT  Max(CASE WHEN Source = 'StoreA' THEN UNIQID END) AS UNIQID_A,
        Max(CASE WHEN Source = 'StoreB' THEN UNIQID END) AS UNIQID_B,
        Max(CASE WHEN Source = 'StoreC' THEN UNIQID END) AS UNIQID_C,
        Max(CASE WHEN Source = 'StoreD' THEN UNIQID END) AS UNIQID_D,
        NAME, AGE
FROM Table
Group By NAME, AGE
user_153326500 
SELECT SUM(UNIQID_A) UNIQID_A, SUM(UNIQID_B) UNIQID_B, SUM(UNIQID_C) UNIQID_C, SUM(UNIQID_D) UNIQID_D, NAME, AGE FROM (
    SELECT CASE WHEN Source = 'StoreA' THEN UNIQID END AS UNIQID_A,
    CASE WHEN Source = 'StoreB' THEN UNIQID END AS UNIQID_B,
    CASE WHEN Source = 'StoreC' THEN UNIQID END AS UNIQID_C,
    CASE WHEN Source = 'StoreD' THEN UNIQID END AS UNIQID_D,
    NAME, AGE
    FROM Table) AS A 
GROUP BY NAME, AGE
user_114983280
Related