Pivot Query

I always love SQL, and it is really pity that I have not written any complex SQL for really long time (I remember I wrote the last complex SQL statement in 2007). Today, I needed a sort of pivot result from SQL Server. Although I knew that I can retrieve the result very easily using pivot in Excel, I wanted to do that in SQL. Sometimes, I like to suffer myself ;)

First, I realized that there is a feature added to the SQL Server 2008 that I was not aware of, called PIVOT and UNPIVOT! It is really nice feature added to T-SQL, yet it was also possible to do such queries in previous SQL servers with a little more difficulties. However, there is a problem that I discuss through definition of table:

The table has three columns, i.e. userID, movieID and rate. We want to have a pivot view which contains movie as rows, users as columns and count of rates as cells. However, in current cyntax, you have to explicitly mention each user by value, which seems not suitable. Thus, I change a SQL which ‘chandra sekhar pathivada’ mentions in 1/17/2013 in the mentioned page to retrieve the result (look at http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx). The SQL that I wrote is:

DECLARE @columns NVARCHAR(1000) SELECT @columns = COALESCE(@columns + ',[' + cast(userID as nvarchar) + ']', '[' + cast(userID as nvarchar)+ ']') FROM(   Select distinct userID   From movies      ) CLMNS DECLARE @query nVARCHAR(4000)            SET @query = '      Select movieID,         '+ @columns +'     FROM        (        select movieID, userID, rate        from         movies ) x        PIVOT        (count(rate) FOR userid IN('+ @columns +')) AS MyPivot'       print @columns  print @query EXECUTE(@query)

This SQL retrieves the result correctly, yet I am still not satisfied. Do you think that it might be other better way to do that? I am still thinking about it.