troubleshooting Question

Need help with a Dynamic query and using a Pivot...

Avatar of sqdperu
sqdperuFlag for United States of America asked on
Microsoft SQL Server* Pivot TablesSQL
5 Comments1 Solution107 ViewsLast Modified:
Using SQL Server 2014 and trying to develop a Dynamic query and Pivot.

My input data looks like this:
 InputData.JPG
My desired output looks like this (I'd like it ORDERED BY order_desc):
OutputData.JPG
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:
 OutputBad.JPG
What am I doing wrong?

Thanks
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros