SQL Pivot Query with dynamic columns

Question

Possible duplicate:
T-SQL Pivot? Ability to create table columns from row values

I searched in vain for too long and must admit defeat and ask for help, I am trying to modify the summary query to create 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 columns with page numbers depend on the number of pages visited by the 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 dealt with this by hard coding the columns, but obviously I don't want to keep changing the script to accommodate more and more pages.

So far, I have something like:

  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])) 

It would be helpful to get any help or pointers in the right direction!

Thanks in advance!

Answer 1

The best example I've seen regarding dynamic rotation is the example of Itzik Ben-Gan. This related SO question has a pretty good example of what you will need to do. Basically, you will need to use dynamic sql to achieve your goal.