具有多列的 SQL Pivot [英] SQL Pivot with multiple columns

问题描述

需要有关 sql server 2008 中的 pivot 子句的帮助.我有一张包含此信息的表格:

Need help with the pivot clause in sql server 2008. I have a table with this info:

Weekno DayOfWeek FromTime ToTime1 2 10:00 14:001 3 10:00 14:002 3 08:00 13:002 4 09:00 13:002 5 14:00 22:003 1 06:00 13:003 4 06:00 13:003 5 14:00 22:00

Weekno    DayOfWeek     FromTime    ToTime
1         2             10:00       14:00
1         3             10:00       14:00
2         3             08:00       13:00
2         4             09:00       13:00
2         5             14:00       22:00
3         1             06:00       13:00
3         4             06:00       13:00
3         5             14:00       22:00

我想把它转换成一个看起来像这样的表格:<前>周开始1结束1开始2结束2开始3结束3开始4结束4开始5结束5开始6结束6开始7结束71 10:00 14:00 10:00 14:002 08:00 13:00 09:00 13:00 14:00 22:003 06:00 13:00 06:00 13:00 14:00 22:00

I want to convert this into a table that looks like this:
Week    Start1    End1    Start2    End2    Start3    End3    Start4    End4    Start5    End5    Start6    End6    Start7    End7
1                         10:00     14:00   10:00     14:00
2                                           08:00     13:00   09:00     13:00   14:00     22:00
3       06:00     13:00                                       06:00     13:00   14:00     22:00

有什么办法可以处理数据透视查询吗?请用一个例子来回答如何做.
Is there any way to do with a pivot query?
Please write respond with an example on how to do it. 
我很感激在这方面的任何帮助.提前致谢.
I appreciate any kind of help on this. Thanks in advance. 
推荐答案
这是枢轴版本:
https://data.stackexchange.com/stackoverflow/query/7295/so3241450
-- SO3241450

CREATE TABLE #SO3241450 (
    Weekno int NOT NULL
    ,DayOfWeek int NOT NULL
    ,FromTime time NOT NULL
    ,ToTime time NOT NULL
)

INSERT INTO #SO3241450 VALUES
(1, 2, '10:00', '14:00')
,(1, 3, '10:00', '14:00')
,(2, 3, '08:00', '13:00')
,(2, 4, '09:00', '13:00')
,(2, 5, '14:00', '22:00')
,(3, 1, '06:00', '13:00')
,(3, 4, '06:00', '13:00')
,(3, 5, '14:00', '22:00')

;WITH Base AS (
    SELECT Weekno, DayOfWeek, FromTime AS [Start], ToTime AS [End]
    FROM #SO3241450
)
,norm AS (
SELECT Weekno, ColName + CONVERT(varchar, DayOfWeek) AS ColName, ColValue
FROM Base
UNPIVOT (ColValue FOR ColName IN ([Start], [End])) AS pvt
)
SELECT *
FROM norm
PIVOT (MIN(ColValue) FOR ColName IN ([Start1], [End1], [Start2], [End2], [Start3], [End3], [Start4], [End4], [Start5], [End5], [Start6], [End6], [Start7], [End7])) AS pvt​


                        
这篇关于具有多列的 SQL Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！