Skip to content
Advertisement

If a column is not null then convert into a row for multiple columns

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
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement