例子:
Col1 Col2 Col3 Col4
A A1 1 2
A A2 4 5
B B1 7 8
B B2 10 11
B B3 13 14
变成:
A A1 Col3 1
A A1 Col4 2
A A2 Col3 4
A A2 Col4 5
B B1 Col3 7
B B1 Col4 8
B B2 Col3 10
B B2 Col4 11
B B3 Col3 13
B B3 Col4 14
求高手帮忙 谢谢
...全文
1083打赏收藏
求复杂SQL 语句
例子: Col1 Col2 Col3 Col4 A A1 1 2 A A2 4 5 B B1 7 8 B B2 10 11 B B3 13 14 变成: A A1 Col3 1 A A1 Col4 2 A A2 Col3 4 A A2 Col4 5 B B1 Col3 7 B B1 Col4 8 B B2 Col3 10 B B2 Col4 11 B B3 Col3 13 B B3 Col4 14 求高手帮忙 谢谢
if exists
(select * from aa )
drop table aa
go
create table aa (aa varchar(2) not null default(2),bb varchar(2),
cc int identity(1,1) primary key,
dd int check(dd<100) )
insert into aa
select 'a','aa',1 union all
select 'b','ab',2 union all
select 'c','ac',6 union all
select 'd','ad',9
select aa,bb ,'cc'as 'cc' ,cc from aa
union all
select aa,bb,'dd' as 'dd',dd from aa
--创建测试数据
CREATE TABLE Test (
COL1 CHAR(1),COL2 CHAR(2),COL3 INT ,COL4 INT
)
INSERT Test SELECT 'A','A1',1,2
UNION ALL SELECT 'A','A2',4,5
UNION ALL SELECT 'B','B1',7,8
UNION ALL SELECT 'B','B2',10,11
UNION ALL SELECT 'B','B3',13,14
--执行
SELECT COL1, COL2,'COL3'AS NewColumn,COL3 AS NewRecord
FROM Test (NOLOCK)
UNION
SELECT COL1, COL2,'COL4'AS NewColumn,COL4 AS NewRecord
FROM Test (NOLOCK)
--删除测试数据
DROP TABLE Test
COL1 COL2 NewColumn NewRecord
A A1 COL3 1
A A1 COL4 2
A A2 COL3 4
A A2 COL4 5
B B1 COL3 7
B B1 COL4 8
B B2 COL3 10
B B2 COL4 11
B B3 COL3 13
B B3 COL4 14