SQL Pivot Query with Dynamic Columns

Question

Possible Duplicate:
T-SQL Pivot? Possibility of creating table columns from row values

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!

Solution

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.

Comments

[Sir Belmalot]

Hi Tim, thanks for the response, although I'm unable to click through to the link you posted?

[Tim Lentine]

@SirBelmalot sorry about that. I updated the link.