Friday, September 20, 2013

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.

Tuesday, July 30, 2013

The SSAS Tutorial for beginners

It was last summer that I developed a new tutorial for SSAS. The aim of this tutorial is to make students familiar with SQL Server 2012 Analysis Services. It might be helpful for you if you are an absolute beginner; otherwise, there is nothing new that you can learn from. The tutorial is based on a fictitious scenario which is grounded on simplified version of AdventureWorks Database.
Unfortunately, I cannot share the data source of this tutorial.