Search code examples
sqltranspose

Transpose data in SQL with NULL values

I have a table with 6 columns like below

ID VAL1 VAL2 VAL3 VAL4 VAL5
1234 AB CD NULL DF NULL
5678 HJ NULL UI NULL NULL

The expected results are like below. I want to transpose the data like below. NULL values shouldn't be considered while transposing.

ID VALUE
1234 AB
1234 CD
1234 DF
5678 HJ
5678 UI

Can I achieve this in SQL? Thanks!

Solution 
  • SELECT *
FROM (
    SELECT id, value1 as value
    FROM table_name
    WHERE value1 IS NOT NULL
UNION
    SELECT id, value2 as value
    FROM table_name
    WHERE value2 IS NOT NULL
) AS nt
ORDER BY id ASC