SQLTeam.com | Weblogs | Forums

Rollup records


#1

Hello all,

anyone know how do I rollup records to just one line per one id?

SELECT id, Type, Color FROM Table1

Output:

01

What I need to achieve is:

02

Anyone?


#2
SELECT q1.id,
    MAX(CASE WHEN q1.row_num = 1 THEN Type ELSE '' END) AS Type1,
    MAX(CASE WHEN q1.row_num = 1 THEN Color ELSE '' END) AS Color1,
    MAX(CASE WHEN q1.row_num = 2 THEN Type ELSE '' END) AS Type2,
    MAX(CASE WHEN q1.row_num = 2 THEN Color ELSE '' END) AS Color2,
    MAX(CASE WHEN q1.row_num = 3 THEN Type ELSE '' END) AS Type3,
    MAX(CASE WHEN q1.row_num = 3 THEN Color ELSE '' END) AS Color3,
    MAX(CASE WHEN q1.row_num = 4 THEN Type ELSE '' END) AS Type4,
    MAX(CASE WHEN q1.row_num = 4 THEN Color ELSE '' END) AS Color4,
    MAX(CASE WHEN q1.row_num = 5 THEN Type ELSE '' END) AS Type5,
    MAX(CASE WHEN q1.row_num = 5 THEN Color ELSE '' END) AS Color5,
    MAX(CASE WHEN q1.row_num = 6 THEN Type ELSE '' END) AS Type6,
    MAX(CASE WHEN q1.row_num = 6 THEN Color ELSE '' END) AS Color6,
    MAX(CASE WHEN q1.row_num = 7 THEN Type ELSE '' END) AS Type7,
    MAX(CASE WHEN q1.row_num = 7 THEN Color ELSE '' END) AS Color7,
    MAX(CASE WHEN q1.row_num = 8 THEN Type ELSE '' END) AS Type8,
    MAX(CASE WHEN q1.row_num = 8 THEN Color ELSE '' END) AS Color8
FROM (
    SELECT id, Type, Color, ROW_NUMBER() OVER(PARTITION BY id ORDER BY Type, Color ) AS row_num FROM dbo.Table1
) AS q1
GROUP BY q1.id
ORDER BY q1.id

#3

Thank you Scott