动态行转列的问题
0
所有回答(2)
0
你这个应该算不上是动态的吧。
CREATE TABLE #TEMP
(
[DATE] DATE,
[TIME] TIME
)
TRUNCATE TABLE #TEMP
INSERT INTO #TEMP
VALUES('2011-11-11','06:30:00'),('2011-11-11','08:30:00'),('2011-11-11','10:30:00'),('2011-11-11','12:30:00'),('2011-11-11','14:30:00'),('2011-11-11','16:30:00')
SELECT * FROM #TEMP
SELECT [DATE] ,
MAX(CASE WHEN [TIME] BETWEEN '06:00:00' AND '08:00:00' THEN [TIME] END) DUTY1,
MAX(CASE WHEN [TIME] BETWEEN '08:00:00' AND '10:00:00' THEN [TIME] END) DUTY2,
MAX(CASE WHEN [TIME] BETWEEN '10:00:00' AND '12:00:00' THEN [TIME]END) DUTY3,
MAX(CASE WHEN [TIME] BETWEEN '12:00:00' AND '14:00:00' THEN [TIME]END) DUTY4,
MAX(CASE WHEN [TIME] BETWEEN '14:00:00' AND '16:00:00' THEN [TIME]END) DUTY5,
MAX(CASE WHEN [TIME] BETWEEN '16:00:00' AND '18:00:00' THEN [TIME]END) DUTY6
FROM #TEMP GROUP BY [DATE]
支持!
0
SQLServer 2005及以上版本有提供 Pivot 方法，可以直接行转列的。
具体可以看下 http://www.cnblogs.com/peaceli/archive/2009/09/08/1561113.html 或者百度 Pivot.
