Selecting multiple row from one row in SQL

Question

I have the following output with me from multiple tables

id    b   c   b     e    b     g  
abc  2   123  3   321   7   876  
abd  2   456  3   452   7   234  
abe  2   0    3   123   7   121  
abf  2   NULL 3   535   7   1212  

Now I want to insert these values into another table and the insert query for a single command is as follows:

insert into resulttable values (id,b,c), (id,b,e) etc.

For that I need to do a select such that it gives me

id,b,c
id,b,e etc

I dont mind getting rid of b too as it can be selected using c# query.

How can I achieve the same using a single query in sql. Again please note its not a table its an output from different tables

My query should look as follows: from the above I need to do something like

select b.a, b.c
union all
select b.d,b.e from  (select a,c,d,e from <set of join>)  b

But unfortunately that does not work

Answer 1

INSERT resulttable
SELECT id, b, c
FROM original
UNION
SELECT id, b, e
FROM original

Your example has several columns named 'b' which isn't allowed...

Answer 2

Here, #tmporigin refers to your original query that produces the data in the question. Just replace the table name with a subquery.

insert into resulttable
select
 o.id,
 case a.n when 1 then b1 when 2 then b2 else b3 end,
 case a.n when 1 then c when 2 then e else g end
from #tmporigin o
cross join (select 1n union all select 2 union all select 3) a

---

The original answer below, using CTE and union all requiring CTE evaluation 3 times

I have the following output with me from multiple tables

So set that query up as a Common Table Expression

;WITH CTE AS (
   -- the query that produces that output
)
select id,b1,c from CTE
union all
select id,b2,e from CTE
union all
select id,b3,g from CTE

NOTE - Contrary to popular belief, your CTE while conveniently written once, is run thrice in the above query, once for each of the union all parts.

NOTE ALSO that if you actually name 3 columns "b" (literally), there is no way to identify which b you are referring to in anything that tries to reference the results - in fact SQL Server will not let you use the query in a CTE or subquery.

The following example shows how to perform the above, as well as (if you show the execution plan) revealing that the CTE is run 3 times! (the lines between --- BELOW HERE and --- ABOVE HERE is a mock of the original query that produces the output in the question.

if object_id('tempdb..#eav') is not null drop table #eav
;
create table #eav (id char(3), b int, v int)
insert #eav select 'abc', 2, 123
insert #eav select 'abc', 3, 321
insert #eav select 'abc', 7, 876
insert #eav select 'abd', 2, 456
insert #eav select 'abd', 3, 452
insert #eav select 'abd', 7, 234
insert #eav select 'abe', 2, 0
insert #eav select 'abe', 3, 123
insert #eav select 'abe', 7, 121
insert #eav select 'abf', 3, 535
insert #eav select 'abf', 7, 1212

;with cte as (
---- BELOW HERE
    select id.id, b1, b1.v c, b2, b2.v e, b3, b3.v g
    from
    (select distinct id, 2 as b1, 3 as b2, 7 as b3 from #eav) id
    left join #eav b1 on b1.b=id.b1 and b1.id=id.id
    left join #eav b2 on b2.b=id.b2 and b2.id=id.id
    left join #eav b3 on b3.b=id.b3 and b3.id=id.id
---- ABOVE HERE
)
select b1, c from cte
union all
select b2, e from cte
union all
select b3, g from cte
order by b1

You would be better off storing the data into a temp table before doing the union all select.