Flatten query result which are rows into side by side columns

Question

I have a parent table

ID    Name
1     abc
2     def

and child table

ID   LineId   Item
1      1      A001
1      2      A002
2      1      B001
2      2      B002

The ID in the child table is the foreign key from the parent table and both ID and LineId make up the primary key in the child table.

I want to join these two table based on 'ID' from both parent and child table to produce a flat result with rows from child tables having the same foreign key become columns, like this:

ID    Name   Item1    Item2 .....
1     abc    A001     A002
2     def    B001     B002

Is the max LineID 2? `MAX(CASE WHEN LineID = 1 THEN Item) Item1, MAX(CASE WHEN LineID = 2 THEN Item) Item2`, grouping by ID and name. For more information, do a search for group by with case aggregation. — ZLK, Jun 22 '16 at 04:19
LineId is just a key for each entry for a specific ID. Its max is not 2 it can be more than 2 if there are more than 2 entries for the ID(foreign key) — Harobed, Jun 22 '16 at 04:22
I ask if the maximum number is 2 because that just makes it a simple `MAX(CASE...` query. e.g. `Select P.ID, P.Name, MAX(CASE WHEN LineID = 1 THEN Item) Item1, MAX(CASE WHEN LineID = 2 THEN Item) Item2 FROM tblParent P JOIN tblChild C ON C.ID = P.ID GROUP BY P.ID, P.Name`. If the LineID can grow to any size then you'll need to use dynamic SQL probably (based on the max LineID) — ZLK, Jun 22 '16 at 04:24
yes, 2 is not the max. the lineid grows depending on the number of entries — Harobed, Jun 22 '16 at 04:28
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Eris, Jun 22 '16 at 04:36
But I have to go with @shA.t answer because he gave the complete query. Appreciate it. — Harobed, Jun 22 '16 at 12:51

score 0 · Accepted Answer · answered Jun 22 '16 at 04:34

You need to use Dynamic SQL like this:

DECLARE @sql nvarchar(max) = 'SELECT p.ID, p.Name';

SELECT @sql = @sql + ',MAX(CASE WHEN c.LineId = ' + CAST(LineId as nvarchar(5)) + ' THEN c.Item END) As Item' + CAST(LineId as nvarchar(5))
FROM childTable
GROUP BY LineId;

SET @sql = @sql + ' FROM parentTable p JOIN childTable c ON p.ID = c.ID GROUP BY p.ID, p.Name';
EXEC(@sql);

Flatten query result which are rows into side by side columns

1 Answers1