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.
