Hi stackoverflow community,
I'm trying to do a self join if the unique ID in Col 1 is the same.
Table code:
CREATE TABLE #table (
Unique_ID int, Product_code varchar(10)
)
INSERT INTO #table (Unique_ID, Product_code) VALUES (1111111111, 1)
INSERT INTO #table (Unique_ID, Product_code) VALUES (1111111111, 2)
INSERT INTO #table (Unique_ID, Product_code) VALUES (1111111111, 3)
INSERT INTO #table (Unique_ID, Product_code) VALUES (2222222222, 4)
INSERT INTO #table (Unique_ID, Product_code) VALUES (2222222222, 4)
INSERT INTO #table (Unique_ID, Product_code) VALUES (3333333333, 5)
INSERT INTO #table (Unique_ID, Product_code) VALUES (3333333333, 6)
INSERT INTO #table (Unique_ID, Product_code) VALUES (3333333333, 6)
INSERT INTO #table (Unique_ID, Product_code) VALUES (3333333333, 3)
#table Input:
Unique_ID Product_code
1111111111 1
1111111111 2
1111111111 3
2222222222 4
2222222222 4
3333333333 5
3333333333 6
3333333333 6
3333333333 3
Desired #table Output:
Unique_ID Product_code Product_code1 Product_code2 Product_code3
1111111111 1 2 3 (Null)
2222222222 4 4 (Null) (Null)
3333333333 5 6 6 3
Current code (Unsure how to compare each row by Unique_ID):
SELECT t1.Unique_ID, t1.Product_code, t2.Product_code AS [Product_code1]
FROM #temp AS t1
JOIN #temp AS t2 ON t1.Unique_ID = t2.Unique_ID
ORDER BY t1.Unique_ID
Any hints and/or help would be much appreciated thanks
Try this. You'll need an intermediate step to relate the different value through an identical column:
select *, seq=identity(int) into #temp from #table order by Unique_ID, Product_code
go
SELECT t1.Unique_ID, t1.Product_code as p1, t2.Product_code as p2, t3.Product_code as p3, t4.Product_code as p4
FROM #temp AS t1
LEFT JOIN #temp AS t2 ON t1.Unique_ID = t2.Unique_ID and t2.seq = t1.seq+1
LEFT JOIN #temp AS t3 ON t2.Unique_ID = t3.Unique_ID and t3.seq = t2.seq+1
LEFT JOIN #temp AS t4 ON t1.Unique_ID = t4.Unique_ID and t4.seq = t3.seq+1
where t1.seq = (select min(seq) from #temp where Unique_ID = t1.Unique_ID)
ORDER BY t1.Unique_ID
go