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

问题描述

可能重复：

我一直在搜索太长时间了，不得不承认失败并要求帮助，我试图修改一个枢纽查询，以产生一个动态查询结果的数据，如下所示：

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-甘的榜样这相关的 SO问题有一个你需要做的很好的例子。基本上，您需要使用一些动态的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屋！