Columns (where value not null) to Other Ordered Columns

Question

TableA contains 6 columns (cola, colb, colc, cold, cole, colf).

A row might contain these values: null, 'orange', 'cat', null, null, 'dog'

I need to return the values where not null into a new table (or view) but in order and into new columns like so:

Col_1, Col_2, Col_3, Col_4, Col_5, Col_6
'orange', 'cat', 'dog', null, null, null

What is the most efficient way to accomplish this?

Answer 1

Honestly, this smells like the real problem is your design; it smells like it's denormalised. The need to resort the values in your columns likely means you are using multiple columns to store the same value multiple times. What you should really be doing is normalising your design by creating a second table which has one row per value. Then you aren't limited to always having 6 values; you could have 1, 9, or even 0.

If you really need to do this, however, one way would be to unpivot your data, renumber them, and then repivot them. If you're really worried about performance, then normalising is also your solution though:

WITH RNs AS(
    SELECT V.YourIDColumn,
           U.I,
           U.Col,
           ROW_NUMBER() OVER (ORDER BY IIF(U.Col IS NULL,1,0),U.I) AS RN
    FROM (VALUES(1,null, 'orange', 'cat', null, null, 'dog'))V(YourIDColumn,ColA,ColB,ColC,ColD,ColE,ColF)
         CROSS APPLY (VALUES(1,ColA),
                            (2,ColB),
                            (3,ColC),
                            (4,ColD),
                            (5,ColE),
                            (6,ColF))U(I,Col))
SELECT YourIDColumn,
       MAX(CASE RN WHEN 1 THEN Col END) AS ColA,
       MAX(CASE RN WHEN 2 THEN Col END) AS ColB,
       MAX(CASE RN WHEN 3 THEN Col END) AS ColC,
       MAX(CASE RN WHEN 4 THEN Col END) AS ColD,
       MAX(CASE RN WHEN 5 THEN Col END) AS ColE,
       MAX(CASE RN WHEN 6 THEN Col END) AS ColF
FROM RNs
GROUP BY YourIDColumn;