列数据转化为行数据

[Alden]

Table1
Key Start End
1 2010-01-01 2010-12-31
2 2011-01-01 2011-12-31

Table2
Key Date Flag
1 2010-01-01 0(start)
2 2010-12-31 1(end)
3 2011-01-01 0(start)
4 2011-12-31 1(end)

如何将Table1的数据转化为Table2格式

[tjs_125]

if object_id( 'Table1') is not null
	Drop Table Table1
create table Table1(
	 keys	int
	,startD datetime
	,endD	datetime)
insert into Table1(keys, startD, endD)
select 1, '2010-01-01', '2010-12-31' union all
select 2, '2011-01-01', '2011-12-31'

select [Key]=Row_number()OVER(ORDER BY b.keys, b.date), 
	[Date] = b.date, 
	Flag =(case 
			when b.aflag = 'startD' then '0(start)'
			when b.aflag = 'endD' then '1(end)' end)
from Table1 a
unpivot 
( date for aflag 
	in (startD, endD)
)as b

[tjs_125]

/*
Key                  Date                    Flag
-------------------- ----------------------- --------
1                    2010-01-01 00:00:00.000 0(start)
2                    2010-12-31 00:00:00.000 1(end)
3                    2011-01-01 00:00:00.000 0(start)
4                    2011-12-31 00:00:00.000 1(end)

(4 行受影响)
*/

[tjs_125]

对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。

[唐诗三百首]

create table Table1
(keys int, startd date, endd date)

insert into Table1
select '1', '2010-01-01', '2010-12-31' union all
select '2', '2011-01-01', '2011-12-31'


select row_number() over(order by t.dates) keys,
t.dates, t.Flag
from
(select startd dates,0 Flag from Table1
union all
select endd dates,1 Flag from Table1) t

keys                 dates      Flag
-------------------- ---------- -----------
1                    2010-01-01 0
2                    2010-12-31 1
3                    2011-01-01 0
4                    2011-12-31 1

(4 row(s) affected)

[Alden]

谢谢，我知道这样写可以，但是能不能通过一个select完成，使用两次select, performance怕是不好吧