sybase/sql self join multiple rows

Question

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

score 0 · Answer 1 · answered Sep 23 '16 at 23:00

0

Since you want 3 product codes per result row, you must do a 3-way self join. Right now you have a 2-way self join, so you must join with #table once more

answered Sep 23 '16 at 23:00

RobV

2,263
1
11
7

I think you misinterpreted my question or it's just not clear the way I wrote it. Either way, I need to move each product_code with the same Unique_ID to a new column. My code currently does not remotely come close to what I'd like to do. – henry91 Sep 26 '16 at 05:08
I think I did -- except I missed that you could have 4 rather than 3 columns. So you need a 4-weay self join, and it needs to be an outer join as well. See the query in the answer (doesn't fit here) – RobV Sep 27 '16 at 22:59

score 0 · Accepted Answer · answered Sep 27 '16 at 23:00

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

Hey Rob, thanks for your help! I've managed to use the idea of your code to create what I need. — henry91, Oct 03 '16 at 00:38

sybase/sql self join multiple rows

2 Answers2

Linked