日期:2014-05-18  浏览次数:20462 次

列数据转化为行数据
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 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)

------解决方案--------------------
SQL code
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