T-SQL PIVOT

[jesh]

I'm in the process of learning about T-SQL and have, just today, discovered the PIVOT keyword. I'm running SQL Server 2005 and have been able to create a pivot table using the PIVOT keyword when I know what the column names should be in advance. Suppose, for a rather stupid example, that I had a table which created a record when a user logs into a website. Something like this:

DECLARE @Test TABLE(	UserID int,	Date datetime,	LoggedIn tinyint)INSERT INTO @Test VALUES (1,'01-22-2007',1);INSERT INTO @Test VALUES (2,'01-22-2007',1);INSERT INTO @Test VALUES (1,'01-23-2007',1);INSERT INTO @Test VALUES (1,'01-23-2007',1);INSERT INTO @Test VALUES (1,'01-24-2007',1);INSERT INTO @Test VALUES (2,'01-24-2007',1);INSERT INTO @Test VALUES (1,'01-25-2007',1);INSERT INTO @Test VALUES (1,'01-26-2007',1);

I can create a Pivot table which tells me how many times certain users log into the site each day like this:

SELECT * FROM @TestPIVOT(COUNT(LoggedIn) FOR UserID IN ([1],[2])) AS p

This produces the following results:

Date				1	 2----------------------------------2007-01-22		  1	 12007-01-23		  2	 02007-01-24		  1	 12007-01-25		  1	 02007-01-26		  1	 0

However, the only way I can do this is if I type in the UserIDs in that IN clause ("[1],[2]"). Does anyone know if it is possible to make this more dynamic?This does not work:

SELECT * FROM @TestPIVOT(COUNT(LoggedIn) FOR UserID IN (SELECT DISTINCT UserID FROM @Test)) AS p

Error: "Incorrect syntax near the keyword 'SELECT'."

[jesh]

OK, I found the answer here:http://msdn.microsoft.com/msdnmag/issues/0...;fig=true#fig13In case someone else comes across this, it basically involves the following steps:1) Using a WHILE loop, loop through all the ids and turn it into a varchar variable (I called it @InPart) that looks like "[1],[4],[12],[22]" depending on your own values.2) Use a nvarchar variable to hold the SQL

DECLARE @Sql nvarchar(1000)SET @Sql = 'SELECT * FROM @Test PIVOT (COUNT(LoggedIn) FOR UserID IN (' + @InPart + ')) AS p'

3) And, using the system stored procedure "sp_executesql", execute the SQL that is in the nvarchar variable

EXEC sp_executesql @Sql