Selecting multiple rows from one row in SQL

Question

I have the following output from me from several 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 paste these values ​​into another table, and the insert request for one command is as follows:

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

To do this, I need to make a choice so that he gives me

id,b,c
id,b,e etc

I do not mind getting rid of b, since it can be selected using a C # request.

How can I achieve the same using a single query in sql. Please note again that this is not a table; its output from different tables

My query should look like this: 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 this does not work

Answer 1

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

In your example, there are several columns named "b" that are not allowed ...

Answer 2

#tmporigin , . .

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

---

, CTE union, CTE 3

;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

. , CTE, , , .

, 3 "b" (), , b , - SQL Server CTE .

, , ( ), , CTE 3 ! ( --- BELOW HERE --- ABOVE HERE original query, .

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

temp, union all.

Answer 3

, ,

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

.

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

. cte.

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

.

declare @T table (a int, c int, d int, e int)
insert into @T values
select a,c,d,e from <set of join>

select b.a, b.c from @T  b
union all
select b.d, b.e from @T  b

, .

Answer 4

, , - :

,

ID Val1 Val2
1 A B
2 C D

reslut like

ID Val
1 A
1 B
2 C
2 D

:

select ID, case when i=1 then Val1 when i=2 then Val2 end as Val
from table
left join ( select 1 as i union all select 2 as i ) table_i on i=i

, . , ( , ). , . Val Val1 Val2 . , , (- = i), - . ( ) - , , . , "BigID, smallID1, smallID2... smallID11", , . , . , 10000 ,

join tab10k on i<=10

for string 10x. I apologize for the stupid formatting, I'm new here.