Hello all,
anyone know how do I rollup records to just one line per one id?
SELECT id, Type, Color FROM Table1
Output:
What I need to achieve is:
Anyone?
Hello all,
anyone know how do I rollup records to just one line per one id?
SELECT id, Type, Color FROM Table1
Output:
What I need to achieve is:
Anyone?
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
Thank you Scott