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:
 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

Open in new window


Here is the output I am getting:
 OutputBad.JPG
What am I doing wrong?

Thanks
sqdperuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
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 : 
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

Open in new window


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

Open in new window


But would like to type it up with some test data first.

Back in a little while...
Mark WillsTopic AdvisorCommented:
This should work for you : 
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

Open in new window

Of course some resulting columns might have NULLS in them, and for that you wouldnt just SELECT * FROM, you would need to spell out the column names and check for NULL values.

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 :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqdperuAuthor Commented:
Mark,

Thanks a lot!  That is exactly what I was trying to do.

Here is the output (I changed the order_desc output heading to "Material"):
OutputGood.JPG
Thanks
sqdperuAuthor Commented:
Thanks
Mark WillsTopic AdvisorCommented:
My pleasure :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.