Need help with a Dynamic query and using a Pivot...
Using SQL Server 2014 and trying to develop a Dynamic query and Pivot.
My input data looks like this:
My desired output looks like this (I'd like it ORDERED BY order_desc):
I want the values under the cnt/number columns to be the "status" field.
If there is some way to add the literal "Kbn" in front of the cnt number in the heading, that would be great too.
NOTE: In this example these order_desc happens to have 5 cnt columns. That is not always the case - so it needs to be dynamic. Sometimes cnt could have 2 or 6 or whatever.
Although for any given run of the query, I believe all the order_desc in that run will have the same number of cnt columns for each order_desc.
Here is the code I have so far:
Here is the output I am getting:
What am I doing wrong?
Thanks
My input data looks like this:
My desired output looks like this (I'd like it ORDERED BY order_desc):
I want the values under the cnt/number columns to be the "status" field.
If there is some way to add the literal "Kbn" in front of the cnt number in the heading, that would be great too.
NOTE: In this example these order_desc happens to have 5 cnt columns. That is not always the case - so it needs to be dynamic. Sometimes cnt could have 2 or 6 or whatever.
Although for any given run of the query, I believe all the order_desc in that run will have the same number of cnt columns for each order_desc.
Here is the code I have so far:
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(cnt)
FROM (SELECT DISTINCT cnt FROM #tmpMfgKbn ) AS Kaban
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT DISTINCT order_desc, ' + @ColumnName + '
FROM #tmpMfgKbn
PIVOT(MAX([status])
FOR cnt IN (' + @ColumnName + ')
) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
Here is the output I am getting:
What am I doing wrong?
Thanks
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You are trying to pivot directly from the data source. The problem with that is the column [status] has already been transformed into one of the @columnames
Now, I would need to test but something more like :
And of course we can get 'kbn' as a prefix in the header....
One way is to first cast the CNT column as the resulting column name, and has to happen when first building the @columnnames, and then in the SELECT order_desc, [status], CNT subquery. In both cases it is a matter of concatenating - easiest to do
But would like to type it up with some test data first.
Back in a little while...
Now, I would need to test but something more like :
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(cnt)
FROM (SELECT DISTINCT cnt FROM #tmpMfgKbn GROUP BY order_desc, cnt, [status]) AS Kaban
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT * FROM
(SELECT order_desc, [status], CNT
FROM #tmpMfgKbn ) src
PIVOT
(MAX([status]) FOR cnt IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
And of course we can get 'kbn' as a prefix in the header....
One way is to first cast the CNT column as the resulting column name, and has to happen when first building the @columnnames, and then in the SELECT order_desc, [status], CNT subquery. In both cases it is a matter of concatenating - easiest to do
'kbn'+ltrim(cnt) as cnt
But would like to type it up with some test data first.
Back in a little while...
This should work for you :
I have written Articles about it (which might give more ideas and explanation) : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
And, if you want to provide test data then I can test for you as well :)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ 'Kbn'+ltrim(cnt)
FROM (SELECT DISTINCT cnt FROM #tmpMfgKbn GROUP BY order_desc, cnt, [status]) AS Kaban
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT * FROM
(SELECT order_desc, [status], ''Kbn''+ltrim(cnt) as cnt
FROM #tmpMfgKbn ) src
PIVOT
(MAX([status]) FOR cnt IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
I have written Articles about it (which might give more ideas and explanation) : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
And, if you want to provide test data then I can test for you as well :)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial