How can I display SQL JOIN results as separate columns?
I am trying to join two tables and then parse the results as separate columns:
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 LEFT JOIN to join tables and GROUP BY to group columns with the same Custmer_ID like below:
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 into separate columns. Is there a way to iterate through the ListID?
+3
source to share
1 answer
This is unique in that the column you want to set does not exist. You can create it using the window function Row_number
. 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 only displays the top three values ListID
in the columns. You can add more values RowNum
if you need more.
+1
source to share