SQL Pivot Query with Dynamic Columns

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
    (SELECT UserId
        ,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!


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.


 ? T-SQL Pivot? Possibility of creating table columns from row values
 ? SQL Server : Querying Multiple Rows, Returning In One
 ? Need to pivot or crosstab a table but not in the conventional way. please
 ? How to write a query which will result in the following format?
 ? How do I PIVOT in SQL Server with dynamic columns, values, and datatypes?
 ? Multi Column Dynamic Pivot Table
 ? Dynamic PIVOT with Numbered Column Names
 ? SQL Server dynamic PIVOT query - fields order
 ? T-SQL dynamic pivot
 ? T-SQL dynamic pivot