Select non-null columns from a row as a list

Question

If I have this table:

+----+--------+--------+--------+--------+
| ID | Field1 | Field2 | Field3 | Field4 |
+----+--------+--------+--------+--------+
| 1  | Foo    |        | Bar    | Baz    |
| 2  |        | Baz    |        |        |
| 3  |        | Dolor  | Bob    |        |
| 4  | Lorem  |        |        | Test   |
| 5  |        | Ipsum  |        |        |
| 6  | Foo    | Bar    | Baz    | Test   |
+----+--------+--------+--------+--------+

How can I select a single row, and have the non-null columns returned as a list?

For example, if I have this (incomplete) statement: SELECT [...] AS Columns FROM [MyTable] WHERE ID = 1, I would like to see this as the result:

+---------+
| Columns |
+---------+
| Foo     |
| Bar     |
| Baz     |
+---------+

How would this select statement look?

seems to be kind of similar to http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings — slartidan, Mar 05 '14 at 15:50
@slartidan No, that's string concatenation, I'm talking about selecting arbitrary columns from a single row, as one column with a bunch of rows (essentially a list). — qJake, Mar 05 '14 at 15:52

Lamak · Accepted Answer · 2014-03-05T16:14:01.600

4

Try this:

SELECT  x.Field
FROM YourTable t
CROSS APPLY 
(
    VALUES
        (t.Field1),
        (t.Field2),
        (t.Field3),
        (t.Field4)
) x (Field);
WHERE t.ID = 1 AND field IS NOT NULL

edited Mar 05 '14 at 16:14

answered Mar 05 '14 at 15:56

Lamak

69,480
12
108
116

This works, but includes null fields. In your example, "Field2" is null and should not be included. How can I filter out null values? – qJake Mar 05 '14 at 16:13
@SpikeX Sorry, I forgot about that requirement. Updated my answer now – Lamak Mar 05 '14 at 16:14
Oops, my fields were actually `''`, not `NULL`, but that was easy to change. Thanks! – qJake Mar 05 '14 at 16:19

score 0 · Answer 2 · answered Mar 05 '14 at 16:16

0

try this,

select feild from 
(select * from @t where id=1)t4
unpivot(orders for feild in(Field1,Field2,Field3,Field4))pvt

answered Mar 05 '14 at 16:16

KumarHarsh

5,046
1
18
22

Select non-null columns from a row as a list

2 Answers2