怎样用一个SQL语句实现?
把表1变成表2:
原表1:
ID A B C D
1 a1 b1 c1 d1
2 a2 b2 c2 d2
3 a3 b3 c3 d3
变成表2:
ID A
1 a1
2 a2
3 a3
4 b1
5 b2
6 b3
7 c1
8 c2
9 c3
10 d1
11 d2
12 d3
多谢!
把表1变成表2:
原表1:
ID A B C D
1 a1 b1 c1 d1
2 a2 b2 c2 d2
3 a3 b3 c3 d3
变成表2:
ID A
1 a1
2 a2
3 a3
4 b1
5 b2
6 b3
7 c1
8 c2
9 c3
10 d1
11 d2
12 d3
多谢!
Table_main
ID xyz
1 S
select
isnull(a.A,isnull(b.B,isnull(c.C,d.D))) as A
from Table_main as main
left join
(
select 'S' as xyz,disp='A',A from Table
) a
on a.xyz=main.xyz
left join
(
select 'S' as xyz,disp='B',B from Table
) b
on b.xyz=main.xyz and b.disp=a.disp
left join
(
select 'S' as xyz,disp='C',C from Table
) c
on c.xyz=main.xyz and c.disp=a.disp and c.disp=b.disp
left join
(
select 'S' as xyz,disp='D',D from Table
) d
on d.xyz=main.xyz and d.disp=a.disp and d.disp=b.disp and d.disp=c.disp
当然这样并不能生成ID,如果你确实想一模一样的话,而又不想在程序里消耗时间,可以用存储过程用做到。
可是这么复杂可不可以只用一个语句实现,可以不用生成ID。表1是在sql企业管理器中,我想编一个VB程序实现这个功能(把表1生成表2的格式)!
select A from 表1
UNION select B as A from 表1
UNION select C as A from 表1
UNION select D as A from 表1;
SELECT A into 表2 from 表1;
Insert Into 表2 select B as A from 表1;
Insert Into 表2 select C as A from 表1;
Insert Into 表2 select D as A from 表1;