• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

SQL PIVOT With Dynamic List...

I've the following pivot statement...

SELECT PartType, PartTypeSize, [000], [050], [075], [100]
FROM (SELECT     PartType, PartTypeSize, Op_Num, SerialNumber FROM vWIP) AS S
PIVOT (Count(SerialNumber) FOR Op_Num IN ([000], [050], [075], [100]) AS D

The values for [000], [050], [075], [100] are pretty crude.  But it works for this example.  Is there a way to dynamically build these values based upon a select statement.  More like...

SELECT PartType, PartTypeSize, (SELECT DISTINCT values FROM table WHERE condition)
FROM (SELECT     PartType, PartTypeSize, Op_Num, SerialNumber FROM vWIP) AS S
PIVOT (Count(SerialNumber) FOR Op_Num IN (SELECT DISTINCT values FROM TABLE WHERE condition) AS D

I figured that I can do this in software and dynamically build the statement. but currently I've this defined in a database view and would like the values to be based upon a changing table.

Possible?  probably not...  Asking for too much probably


0
allidap
Asked:
allidap
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You can do it in SQL in the same way you would in software using dynamic SQL statement.  You would query and build a string representing your SQL statement with hardcoded column names (except you didn't have to hand type them) and you execute like this:

EXEC(@dynamicSQL)
0
 
allidapAuthor Commented:
Thanks for the help.
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now