I have a table like below where I looking to transpose col4 col5 and col6 into rows but in a specific pattern that I listed below
col1 | col2 | col3 | col4 | col5 | col6 |
---|---|---|---|---|---|
a | b | c | 500 | 200 | |
w | x | y | 1000 | 300 | |
z | g | h | 200 | 600 |
I want to convert it to the following
col1 | col2 | col3 | col4 | col5 | col6 |
---|---|---|---|---|---|
a | b | c | 500 | ||
a | b | c | 200 | ||
w | x | y | 1000 | ||
w | x | y | 300 | ||
z | g | h | 200 | ||
z | g | h | 600 |
I am trying this with unpivot but unable to get the desired result
Basically, if the null value is found in one of the columns for instance first record in col4 then the SQL query should ignore col4 which has the null value but transpose a b c col5 (500) into a row plus a b c col6 (200) into another row
Advertisement
Answer
CROSS APPLY
combined with UNION ALL
is very useful here:
SELECT t.col1, t.col2, t.col3, v.* FROM table t CROSS APPLY ( SELECT col4, NULL, NULL WHERE col4 IS NOT NULL UNION ALL SELECT NULL, col5, NULL WHERE col5 IS NOT NULL UNION ALL SELECT NULL, NULL, col6 WHERE col6 IS NOT NULL ) v
If you have many columns this gets tedious. Futhermore, this type of table design is generally incorrect. What you need is a straightforward UNPIVOT
:
SELECT upvt.col1, upvt.col2, upvt.col3, upvt.ColName, upvt.Value FROM table t UNPIVOT ( Value FOR ColName IN (Col4, Col5, Col6, Col7, Col8, Col9) ) upvt