Add column with unique row value

Question

I have a table like this:

SELECT 
    parent_id,  
    parent2_id,   
    parent3_id,  
    parent4_id  
FROM 
    parents

I need to make it like this:

I need to add column with an unique value of a row, but since we have 4 columns, then there are four rows. But, if anyone can make this result table without zero value in id column, it would be very cool.

upd: I found an answer, but it seems very large. Can the solution be simplified?

You need to repeat each column 4 times? And give each a unique ID? What are the zero IDs for? — Schwern, Nov 07 '22 at 18:12
You can do it with a UNION or with a lateral join if your DBMS supports it. — Serg, Nov 07 '22 at 18:15
If I understood the logic behind it besides the lack of explanation, OP wants the ID column to have one of each of the parent_id's except for the ones with 0 value — Jorge Campos, Nov 07 '22 at 18:15

score 0 · Accepted Answer · edited Nov 07 '22 at 19:16

SELECT * FROM 
(SELECT 
    parent_id as id,
    parent_id,  
    parent2_id,   
    parent3_id,  
    parent4_id  
FROM 
    parents
UNION ALL
SELECT parent2_id as id,
    parent_id,  
    parent2_id,   
    parent3_id,  
    parent4_id  
FROM 
    parents
UNION ALL
SELECT parent3_id as id,
    parent_id,  
    parent2_id,   
    parent3_id,  
    parent4_id  
FROM 
    parents
UNION ALL
SELECT parent4_id as id,
    parent_id,  
    parent2_id,   
    parent3_id,  
    parent4_id  
FROM 
    parents) as src
WHERE src.id != 0

Add column with unique row value

1 Answers1