用UNION 就可以!--创建测试数据 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
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
(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