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!
2
One possible way which will work on almost every dbms is using UNION ALL. Then in an outer query filter all null VAL values.
Query,
SELECT id, VAL
FROM (
SELECT id,val1 AS VAL FROM myTable
UNION ALL
SELECT id,val2 FROM myTable
UNION ALL
SELECT id,val3 FROM myTable
UNION ALL
SELECT id,val4 FROM myTable
UNION ALL
SELECT id,val5 FROM myTable
) tbl
WHERE VAL IS NOT NULL
ORDER BY id ASC
This can be done without outer query as well, adding an where clause such as the following , but I would prefer using an outer query for readability .
SELECT id,val1 AS VAL FROM myTable
WHERE val1 IS NOT NULL
UNION ALL
SELECT id,val2 FROM myTable
WHERE val2 IS NOT NULL
UNION ALL
SELECT id,val3 FROM myTable
WHERE val3 IS NOT NULL
UNION ALL
SELECT id,val4 FROM myTable
WHERE val4 IS NOT NULL
UNION ALL
SELECT id,val5 FROM myTable
WHERE val5 IS NOT NULL
ORDER BY id ASC
See example
There are some functions such as UNPIVOT (SQL Server) etc but those are product specific and you need to tag the dbms used.
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
0