行值转列

Question

数据源：
create table TB1(WEEK INT ,mon DATETIME,tue DATETIME,wed DATETIME,thu DATETIME,fri DATETIME,sat DATETIME,sun DATETIME)
insert TB1 select 141,'2014-04-11','2014-04-12','2014-04-13','2014-04-14 ','2014-04-15','2014-04-16','2014-04-17'
insert TB1 select 142,'2014-04-18','2014-04-19','2014-04-20','2014-04-21','2014-04-22','2014-04-23','2014-04-24'

Answer 1

UNION一下就可以了
select WEEK,mon as D FROM TB1
UNION ALL
select WEEK,tue as D FROM TB1
UNION ALL
……

Answer 2

SQL2005还可以用SELECT U1.WEEK,U1.VALUE FROM TB1 UNPIVOT(VALUE FOR TYPE1 IN(mon,tue,wed,thu,fri,sat,sun))U1