Showing posts with label PIVOT. Show all posts
Showing posts with label PIVOT. Show all posts

December 24, 2012

SQL SERVER: Cross tab Query with Pivot (Dynamic Pivot)

As I mentioned earlier, PIVOT, in my earlier post, where we have reviewed the PIVOT with static columns. In practical world, we may need to have dynamic columns that needs to be displayed as below:
 
SQLYoga Dynamic PIVOT with SQL SERVER
As Mentioned in Image, Here, dates are dynamic, based on Order Date, 10/25,10/26,10/27,10/28 etc..To achieve the same with Dynamic columns please find query for the same:
 
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'

DECLARE @MinDate DATE
	,@MaxDate DATE
DECLARE @Dates VARCHAR(MAX)

SELECT @Dates = ''

SELECT @MinDate = MIN(OrderDate)
	,@MaxDate = MAX(OrderDate)
FROM #TestTable

WHILE @MinDate <= @MaxDate
BEGIN
	SELECT @Dates = @Dates + '[' + CONVERT(VARCHAR(10), @MinDate, 101) + '],'

	SELECT @MinDate = DATEADD(d, 1, @MinDate)
END

SELECT @Dates = LEFT(@Dates, LEN(@Dates) - 1)

DECLARE @qry NVARCHAR(MAX)

SELECT @qry = N'

SELECT    id,
        ' + @Dates + '
FROM (
    SELECT    id,
            ISNULL(Amount,0) AS Amount,
            OrderDate
    FROM #TestTable t
) x
PIVOT(
    SUM(Amount) FOR OrderDate IN(' + @Dates + ')
) p '

EXEC (@qry)

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

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)

December 20, 2009

SQL SERVER: Presentation at Ahmedabad User Group Meeting

Last Saturday, 19th December 2009, I presented session on "Write CROSS TAB Query with PIVOT".
 
This was a regular User Group meeting held every third Saturday of month. I have presented session with Jacob Sebastian (SQL SERVER MVP) and Pinalkumar Dave (SQL SERVER MVP).
 
If you need PowerPoint Presentation and Demo script, send me an email at:tejasnshah.it@gmail.com. If you want to register for Ahmedabad User Group meeting, please register at: Click Here
 
DSC00144
Tejas Shah (Me), Presenting session
DSC00150
Tejas Shah (Me, Presenting session
DSC00149
Pinal Dave (SQL SERVER MVP), Presenting session
DSC00156
User Group Members with President, Jacob Sebastian, Pinal Dave
UG Group Photo
If you want to register for Ahmedabad User Group meeting, please register at: Click Here