user2168066 user2168066 - 6 months ago 14
SQL Question

Return union query into multiple columns

I have 4 different select statemnts that union the results and display the orders like so.

lab_orders MED111
lab_orders MED262383
mg_orders MED262383
mg_orders UNT222
pcg_pharmacogenetics MED262371
pcg_pharmacogenetics UNT248234
well_wellness UST601061
well_wellness UNTAccession
well_wellness UST333


Using this query.

select 'mg_orders',csv as csv1
from (select csv from csvdata where csv not in(select orderid from mg_orders)) as t1
union
select 'lab_orders',csv as csv2
from (select csv from csvdata where csv not in(select orderid from lab_orders)) as t2
union
select 'pcg_pharmacogenetics',csv as csv3
from (select csv from csvdata where csv not in(select orderid from pcg_pharmacogenetics)) as t3
union
select 'well_wellness',csv as csv4
from (select csv from csvdata where csv not in(select orderid from well_wellness)) as t4


What I want to do is return the result set in separate columns, I have tried the following but get "no column named csv2"

select csv1, csv2, csv3, csv4
from(
select 'mg_orders',csv as csv1
from (select csv from csvdata where csv not in(select orderid from mg_orders)) as t1
union
select 'lab_orders',csv as csv2
from (select csv from csvdata where csv not in(select orderid from lab_orders)) as t2
union
select 'pcg_pharmacogenetics',csv as csv3
from (select csv from csvdata where csv not in(select orderid from pcg_pharmacogenetics)) as t3
union
select 'well_wellness',csv as csv4
from (select csv from csvdata where csv not in(select orderid from well_wellness)) as t4
) as t


lab_orders mg_orders pcg_pharmacogenetics
MED111 MED262383 MED262371
MED262383 UNT222 UNT248234

vkp vkp
Answer

When you are using union, the column-names or aliases from the first query would be used for the result-set. In your case the result set would contain mg_orders and csv1 as column headers.

I assume you are looking for a query similar to

select 
max(case when csv not in(select orderid from mg_orders) then csv end) as csv1,
max(case when csv not in(select orderid from lab_orders) then csv end) as csv2,
max(case when csv not in(select orderid from pcg_pharmacogenetics) then csv end) as csv3,
max(case when csv not in(select orderid from well_wellness) then csv end) as csv4
from csvdata
Comments