Convert multi columns into a single column-CodePudding

I am trying to come up with select statement that will take multiple columns and put them into a single column For example, if I have data like the below:

id  d1      d2
1   R04.0   
1   Z00.129 Z13.220
2   Z00.129 Z68.36
2   Z23 
3   Z23

I want the data to look like this

id  column 1
1   R04.0
1   Z00.129
1   Z13.220
2   Z00.129
2   Z23
2   Z68.36
3   Z23

I tried looking at pivot tables, but I don't think that is what I am looking for. I can probably write a cursor, but that seems very inefficient.

CodePudding user response：

A UNION ALL should do the trick.

SELECT column1 = d1 FROM data WHERE d1 IS NOT NULL
UNION ALL
SELECT column1 = d2 FROM data WHERE d2 IS NOT NULL

CodePudding user response：

You may try union:

Select id, d1 column1 from tab1 where d1 is not null
union all
Select id, d2 from tab1 where d2 is not null

And if you’d like to order by id, you may

Select * from (Select id, d1 column1 from tab1 where d1 is not null
union all
Select id, d2 from tab1 where d2 is not null) order by id

CodePudding user response：

Because the other answers are either incomplete or overly complex, here is another alternative.

Note I am assuming that d1 is never null/empty but that d2 might be and that you want your results ordered by id and col1

select id, d1 col1
from MyTable

union all

select id, d2 col1
from MyTable
where d2 is not null

order by id, col1;