I have a table table1 like below
+----+------+------+------+------+------+
| id | loc | val1 | val2 | val3 | val4 |
+----+------+------+------+------+------+
| 1 | loc1 | 10 | 190 | null | 20 |
| 2 | loc2 | 20 | null | 10 | 10 |
+----+------+------+------+------+------+
need to combine the val1 to val4 into a new column val with a row for each so that the output is like below.
NOTE: - I data I have has val1 to val30 -> ie. 30 columns per row that need to be converted into rows.
+----+------+--------+
| id | loc | val |
+----+------+--------+
| 1 | loc1 | 10 |
| 1 | loc1 | 190 |
| 1 | loc1 | null |
| 1 | loc1 | 20 |
| 2 | loc2 | 20 |
| 2 | loc2 | null |
| 2 | loc2 | 10 |
| 2 | loc2 | 10 |
+----+------+--------+
I'm sure there's a classier approach than this.
SELECT * FROM (
select id, loc, val1 as val from #t a
UNION ALL
select id, loc, val2 as val from #t a
UNION ALL
select id, loc, val3 as val from #t a
UNION ALL
select id, loc, val4 as val from #t a
) x
order by ID
Here's my attempt with unpivot but cant get the nulls, perhaps perform a join for the nulls? Anyway i'll still try
SELECT *
FROM (
SELECT * FROM #t
) main
UNPIVOT (
new_val
FOR val IN (val1, val2, val3, val4)
) unpiv