列数据转化为行数据解决办法

列数据转化为行数据
Table1
KeyStartEnd
12010-01-012010-12-31
22011-01-012011-12-31

Table2
KeyDateFlag
12010-01-010(start)
22010-12-311(end)
32011-01-010(start)
42011-12-311(end)

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

[解决办法]

SQL code

create table Table1(keys int, startd date, endd date)insert into Table1select '1', '2010-01-01', '2010-12-31' union allselect '2', '2011-01-01', '2011-12-31'select row_number() over(order by t.dates) keys,t.dates, t.Flagfrom(select startd dates,0 Flag from Table1union allselect endd dates,1 Flag from Table1) tkeys                 dates      Flag-------------------- ---------- -----------1                    2010-01-01 02                    2010-12-31 13                    2011-01-01 04                    2011-12-31 1(4 row(s) affected)
[解决办法]
SQL code
if object_id( 'Table1') is not null    Drop Table Table1create table Table1(     keys    int    ,startD datetime    ,endD    datetime)insert into Table1(keys, startD, endD)select 1, '2010-01-01', '2010-12-31' union allselect 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 aunpivot ( date for aflag     in (startD, endD))as b