具有动态列的 SQL 透视查询 [英] SQL Pivot Query with Dynamic Columns

问题描述

可能重复:
T-SQL 数据透视?从行值创建表列的可能性

我一直在徒劳地搜索太久，不得不承认失败并寻求帮助，我正在尝试修改数据透视查询以从具有以下数据的表中生成动态查询结果:

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!

提前致谢！

推荐答案

我见过的关于动态旋转的最好例子是 Itzik Ben-Gan 的例子.这个相关的 SO Question 有一个你需要做什么的很好的例子.基本上，您需要使用一些动态 sql 来实现您的目标.

The best example I've seen regarding dynamic pivoting is Itzik Ben-Gan's example. This related SO Question has 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.

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