November 5, 2012

SQL SERVER: Cross tab query with PIVOT

We had a User group meeting for "Cross Tab Queries", where I have explained how to write PIVOT queries. Many times developer has an issue while writing query with PIVOT. Let me have a sample code for query with PIVOT keyword.
 
PIVOT is used to transform rows to column, which gives result in such a way which can be send to the user directly. Let me take an example. We have an order table where we have each order stored in a row in a table as displayed below:
 
ctrl_no id Amount OrderDate
1000029 100016 990 10/25/2012
1000029 100018 990 10/25/2012
1000029 100016 660 10/26/2012
1000029 100018 660 10/26/2012
1000029 206007 660 10/26/2012
1000029 206007 660 10/27/2012
This information has to be saved in a row in relational database. When this information needs to be display it on the application, it needs to be displayed as below, so user can use this info for decision making:
id 10/25/2012 10/26/2012 10/27/2012
100016 990.00 660.00 NULL
100018 990.00 660.00 NULL
206007 NULL 660.00 660.00
Did you see the difference? how easy user can review the information. Lets see an query how to achieve the expected result with PIVOT keyword.
CREATE TABLE #TestTable (
	ctrl_no INT
	,id INT
	,Amount NUMERIC(18, 2)
	,OrderDate DATE
	)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

SELECT id
	,[10/25/2012]
	,[10/26/2012]
	,[10/27/2012]
FROM (
	SELECT id
		,Amount
		,OrderDate
	FROM #TestTable t
	) x
PIVOT(SUM(Amount) FOR OrderDate IN (
			[10/25/2012]
			,[10/26/2012]
			,[10/27/2012]
			)) p

DROP TABLE #TestTable
 
Let me know if you have any question/comment.
Reference: Tejas Shah (www.SQLYoga.com)

No comments:

Post a Comment