具有多个列的SQL Pivot [英] SQL Pivot with multiple columns
本文介绍了具有多个列的SQL Pivot的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
需要有关sql server 2008中的数据透视子句的帮助. 我有一张桌子,上面有以下信息:
Need help with the pivot clause in sql server 2008. I have a table with this info:
Weekno DayOfWeek FromTime到时间 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:00Weekno 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结束7 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:00I 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屋!
查看全文