CODEWITHSUNDEEP
sqlsql-server
qJake
qJake

Reputation: 17139

Select non-null columns from a row as a list

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?

Upvotes: 0

Views: 42

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5088

try this,

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

Upvotes: 0

Lamak
Lamak

Reputation: 70668

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

Upvotes: 4

Related Questions