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

You can use apply and then filter out the all-NULL values:

select t.col1, t.col2, t.col3, v.*
from t cross apply
     (values (col4, null, null), (null, col5, null), (null, null, col6)
     ) v(col4, col5, col6)
where v.col4 is not null or v.col5 is not null or v.col6 is not null;

Answer 2

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

Answer 3

In general there are two approaches (well, rather three):

1. Use a lot of typing to create a statement covering all your columns. This is ugly but fast. The biggest draw-back: Adding a column in the future will force you to re-do your queries.
2. Use a generic approach, which will work with any count of columns. The biggest draw-back: This won't be fast.
3. Dynamic SQL: Use Metadata to create the statement of suggestion 1. dynamically. The biggest draw-back: This will never work in ad-hoc/inline queries.

To show you one generic approach you might test this:

DECLARE @tbl TABLE(col1 VARCHAR(10),col2 VARCHAR(10),col3 VARCHAR(10),col4 INT,col5 INT,col6 INT);
INSERT INTO @tbl VALUES
 ('a','b','c',NULL,500,200)
,('w','x','y',1000,300,NULL)    
,('z','g','h',200,NULL,600);

--The generic unpivot query

SELECT t.col1,col2,col3
        ,ROW_NUMBER() OVER(PARTITION BY col1,col2,col3 ORDER BY B.attr) AS GroupIndex
        ,B.attr.value('local-name(.)','nvarchar(max)') ColumnName
        ,B.attr.value('.','int') ColumnValue
FROM @tbl t
CROSS APPLY(SELECT(SELECT t.* FOR XML RAW,TYPE)
            .query('<cols>{/row/@*[not(local-name()=("col1","col2","col3"))]}</cols>')) A(x)
CROSS APPLY A.x.nodes('/cols/@*') B(attr);

--This we can use within a PIVOT query

SELECT p.*
FROM
(
    SELECT t.col1,col2,col3
          ,ROW_NUMBER() OVER(PARTITION BY col1,col2,col3 ORDER BY B.attr) AS GroupIndex
          ,B.attr.value('local-name(.)','nvarchar(max)') ColumnName
          ,B.attr.value('.','int') ColumnValue
    FROM @tbl t
    CROSS APPLY(SELECT(SELECT t.* FOR XML RAW,TYPE)
                .query('<cols>{/row/@*[not(local-name()=("col1","col2","col3"))]}</cols>')) A(x)
    CROSS APPLY A.x.nodes('/cols/@*') B(attr)
) intermediateResult
PIVOT
(
    MAX(ColumnValue) FOR ColumnName IN(col4,col5,col6)
)p;

The idea in short:

• We use APPLY create an intermediate XML representation of your columns behind col1,col2 and col3. In order to achieve this, we first create a XML and use query() to return all columns except col1, col2, col3 as attributes in each row.
• We use XML's default to omit NULL values
• We add this as column A.x to the result set.
• Another APPLY will call the XML method .nodes(). This will add one row per existing attribute.
• The intermediateResult is an unpivoted set. This is the format you should use for storage actually...
• Now we can use PIVOT to get the output wanted.

The biggest advantage is, that you can add col7 without any need to change this (in the generic part). The output-list of PIVOT will need any new column explicitly.