Query question - multiple columns to 1 column

[Mike]

Hi, I have a query that has the following values:

ID Value1 Value2
1 Test1 <Null>
2 <Null> <Null>
3 Test2 Test3

I would like to get the following out of the above query:

ID Value
1 Test1
1 <Null>
2 <Null>
2 <Null>
3 Test2
3 Test3

Is this possible and how?

Thanks, D.

 

[peregenem]

Hi, I have a query that has the following values:

ID Value1 Value2
1 Test1 <Null>
2 <Null> <Null>
3 Test2 Test3

I would like to get the following out of the above query:

ID Value
1 Test1
1 <Null>
2 <Null>
2 <Null>
3 Test2
3 Test3

SELECT ID, Value1,
IIF(Value1 IS NULL, 1, 0) AS sort_order
FROM MyTable
UNION ALL
SELECT ID, Value2,
IIF(Value2 IS NULL, 1, 0) AS sort_order
FROM MyTable
ORDER BY 1, 3, 2;

It is good practise to expose the sort_order 'helper' column

 

[Mike]

Thanks for your reply, works perfect!

However, can you also help me with a WHERE condition, so that I only
see the 0 value in column sort_order?

Thanks again, D.

 

[Mike]

Nevermind, got it already, sorry!

D.