带有动态列的 SQL Pivot 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8343417/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Pivot Query with Dynamic Columns
提问by Sir Belmalot
Possible Duplicate:
T-SQL Pivot? Possibility of creating table columns from row values
可能的重复：
T-SQL Pivot？从行值创建表列的可能性
I've been searching in vain for too long now and have to admit defeat and ask for help, I'm trying to modify a pivot query to produce a dynamic query of results from a table with data like this:
我一直在徒劳地搜索太久了，不得不承认失败并寻求帮助，我正在尝试修改数据透视查询以从具有如下数据的表中生成结果的动态查询：
UserId PageViewed DateTimeStamp
1 Index.html 2011-12-01 13:55:01
1 FAQ.html 2011-12-01 13:58:53
1 ContactUs.html 2011-12-01 14:00:16
2 Index.html 2011-12-01 15:55:01
2 FAQ.html 2011-12-01 15:58:53
2 ContactUs.html 2011-12-01 15:00:16
To show something like this, where the page number columns depend on the number of pages visited by a user:
要显示这样的内容，其中页码列取决于用户访问的页面数：
User StartTime Page1 Page2 Page3
1 13:55:01 Index.hml FAQ.html ContactUs.html
2 15:55:01 Index.hml FAQ.html ContactUs.html
I've managed it by hard coding the columns in, but obviously I don't want to keep changing the script to accomodate more and more pages.
我通过对列进行硬编码来管理它，但显然我不想继续更改脚本以容纳越来越多的页面。
So far I have something along the lines of:
到目前为止，我有一些类似的东西：
SELECT p.UserId,
CONVERT(TIME, MIN(p.DateTimeStamp), 7) StartTime,
ISNULL(p.[1],'') Page1
ISNULL(p.[1],'') Page2
ISNULL(p.[1],'') Page3
FROM
(SELECT UserId
,DateTimeStamp
,PageViewed
,ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY DateTimeStamp) as pOrder
FROM tbl) AS p
PIVOT(MIN(PageViewed) FOR pOrder IN ([1],[2],[3]))
Any help or pointers in the right direction would be greatly appreciated!
任何正确方向的帮助或指示将不胜感激！
Thanks in advance!
提前致谢！
采纳答案by Tim Lentine
The best example I've seen regarding dynamic pivoting is Itzik Ben-Gan's example. This related SO Questionhas a pretty good example of what you would need to do. Basically, you'll need to use some dynamic sql in order to accomplish your goal.
我见过的关于动态旋转的最好例子是 Itzik Ben-Gan 的例子。这个相关的SO Question有一个很好的例子说明你需要做什么。基本上，您需要使用一些动态 sql 来实现您的目标。
