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

Question

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

Answer 1

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