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格式
------解决方案--------------------
- 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