RSS

PIVOT Table – SQL Server !!

28 May

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values. You can see the following table and the next results to understand the PIVOT transformation:

01

You can see table structure from here

To start pivoting the table to get a report represents the count of every product has been sold to this customer, you can run this script:

SELECT * FROM Pivot_Test
PIVOT (SUM([Quantity]) FOR [ProductName] IN ([Hard Disk], [Headphones], [Mother Board], [RAM], [Wireless Mouse])) AS PVT

Then, you will get this result:

02

Notice: If you don’t know the values that you want pivoting depends on, you will have a problem because you want to use IN operator with a sub-query to get a unique values from the main table and pivot the table depending on them. So you can see the following script to do this:

DECLARE @ColumnsNames NVARCHAR(MAX), @strSQL NVARCHAR(MAX);

SELECT @ColumnsNames = ISNULL(@ColumnsNames + ‘, ‘, ”) + ‘[‘ + tbl.ProductName + ‘]’
FROM (SELECT DISTINCT(ProductName) FROM [dbo].[Pivot_Test]) AS tbl

SET @strSQL = N’SELECT * FROM Pivot_Test PIVOT (SUM([Quantity]) FOR [ProductName] IN (‘ + @ColumnsNames + ‘)) AS PVT’

EXEC sp_executesql @stmt = @strSQL The result is:

03

 
Leave a comment

Posted by on May 28, 2013 in MS SQL Server

 

Tags: , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: