How to display results from SQL JOIN as separate columns?

Question

I am trying to join two tables and then parse out the results into separate columns like so:

Table1:

Customer_ID
----------
1
2
3

Table2:

Customer_ID ListID
------------------
1           1
1           2
1           5
2           1
2           3

Desired Results:

Customer_ID ListID1 ListID2 ListID3
-----------------------------------
1           1       2       5
2           1       3   
3

I used a LEFT JOIN to combine the tables and a GROUP BY to group columns with the same Custmer_ID as shown here:

SELECT MIN([Table1].[Customer_ID])
    ,MIN([Table2].[ListID])
FROM [Table1]
LEFT JOIN [Table2]
ON [Table2].[Customer_ID] = [Table1].[Customer_ID]
GROUP BY [Table1].[Customer_ID]

Current Results:

Customer_ID ListID
------------------
1           1
2           1
3           NULL

I can't figure out where to go from here to parse the ListID's into separate columns. Is there a way to iterate through the ListID's?

Please look into pivot function http://stackoverflow.com/questions/14797691/dynamic-pivot-columns-in-sql-server — Hituptony, Jun 24 '15 at 21:10

score 1 · Accepted Answer · answered Jun 24 '15 at 22:34

This is unique in that the column that you want to pivot for doesn't exist. You can create it with the Row_number window function. It looks like this:

SELECT Customer_ID, [1] ListID1, [2] ListID2, [3] ListID3
FROM
(select Table1.Customer_ID, 
    Table2.ListID,
    ROW_NUMBER() over (Partition by Table1.Customer_ID Order By Table2.ListID) RowNum
from Table1
LEFT JOIN Table2
ON Table2.[Customer_ID] = Table1.[Customer_ID]) as SourceTable
PIVOT
(
max(ListID)
FOR RowNum IN ([1], [2], [3])
) AS PivotTable

This will only show the top three ListID values in the columns. You can add more RowNum values if you need more.

This worked, thank you soo much!! I will definitely go and research more on pivot tables. — user3669766, Jun 25 '15 at 14:39

How to display results from SQL JOIN as separate columns?

1 Answers1