PIVOT statement is required explicittly indicate a list of values that will be the columns to be created. The trouble is that is not DINAMIC and if a new value appear, a modification must be done for accept the new one.
Upvotes: 100<=-=Nov 23 2005 3:16PM=-=>
Thank you Yoshiko for making us aware of this customer request. What makes this request difficult to fulfill technically is that the inner subquery would determine the shape of the output result set. The way SQL Server is built, the shape of a relational result set needs to be determined independently of the data the query accesses. I understand the business reasoning of having such a facility though, so we are keeping this issue active for the next version of the product to see what alternate solutions we can come up with for this meaningful scenario.
SQL Server Development
I’d like to further request the ability to order the pivoted columns by optionally specifying the order by clause in the subquery. Right now, the only way to pivot the columns server-side is to modify the workaround I posted to include an ORDER BY. That works somewhat by chance, because sql just happens to order the columns in the same order you list the values in. But, these work-arounds all require stored procedures. I’m confident Microsoft can find a way to evaluate the inner-most query first, to determine the outermost (pivot) query so that these highly useful PIVOTs could be done in a single t-sql statement. Of the hundreds of times I’ve needed to PIVOT data, not once have I known the list of values beforehand. I’ve always needed the ability to specify this dynamically at runtime. Thanks for your continued research into this issue.<=-=Nov 20 2008 8:56AM=-=>
I also strongly feel the need. This limitation makes it hard to work with property bags in SQL Server.
Great syntax would be allowing a query instead of the current set of column names. Thus
PIVOT … FOR [Name] IN (SELECT DISTINCT [Name] FROM [PROPERTIES])
I’m interested to know what you would do with the output of a dynamic pivot.
Anything that consumes the result of a SELECT statement (e.g. SSIS, SSRS, a view, an INSERTion) expects that the “shape” (i.e. the metadata) is in a known state – that wouldn’t be the case with a dynamic pivot.
The only possible way that I can imagine this being consumed is a table with a dynamic schema. Such a thing doesn’t exist today but I guess could be made possible using SPARSE columns.
-Jamie<=-=Feb 1 2011 8:39PM=-=>
I too would like to see dynamic determination of the output columns. With regard to the consumption question, I think that the primary consumer would be human (the person writing the query). I often use PIVOT during interactive data analysis. This requires me to write SQL to determine the unique set of output columns, then go through a manual editing process to convert them into the PIVOT clause.
While I’m willing to do so for code that will live on, I’d rather not have to do so for a quick&dirty data analysis task.
With regard to performance, I assume that such a capability would require two passes through the input (first to determine the range out outputs, and a second to actually perform the pivot). For small datasets, that’s acceptable. For large datasets, the performace of PIVOT (which appears to be implemented as a series of CASE statements) is nearly unacceptable anyway. (see another suggestion).
Lastly, if we can’t have a DML implementation of this feature, how about a SSMS helper? Can I right-click on a table an get an option that will scan the table and generate the DML for me?<=-=Mar 14 2011 1:15PM=-=>
To respond to Jamie, My thing that consumes a dynamic pivot is an application in ado.net. I’ve written some horrible SQL Sproc code that dynamically makes a list of IDs for the pivot, and then executes it through Exec(’’) and passes that back to my application – which knows that the first two columns are fixed, and all the rest are the pivot. So yes, using a dynamic pivot is very useful.<=-=Jul 31 2011 9:46AM=-=>
@Jamie – I’ve used dynamic SQL based pivoted results before to bind to things like GridViews it definitely has its uses. I’ve also seen a fair bit of messy SQL injection prone code around tackling this problem.<=-=Sep 21 2011 7:45AM=-=>
If the ability to Pivot on a Subquery existed we could create Views with variable column counts/names that could be ported over to Analysis Services. I can’t be the only person who would love this.
Let’s think of this in terms of maintainability. (Granted I understand a maximum capacity on tables, but let us handle that…)
Real World Scenario:
I have a Master Record Table.
Each Master Record can have any number of Events.
The records in the Events table have time stamps for Create and Complete and a type column that links to an EventsType table.
Each Type Record has a Description and a sub-type.
I want multiple views that show all Master Records with each EventType Description as a Column for a specified subtype.
The Data in the rows for each column should show the Max Complete time stamp, or null if not completed.
A View with either crazy amounts of self Joins with Subqueries and aliases, or a Pivot with individually specified Descriptions for the IN clause.
The thought of having to add another Join or value to the IN clause every time a column is added to a table is insane.
Suggested Alternative: (PLEASE!)
We could make a view that Stuffs and QuoteNames some Distinct values from the column we want to work with. Then in the Pivot IN clause we can select the appropriate value set we want from the “Columns” View. Now it’s guaranteed that the value set for the Pivot IN clause is properly formatted. Beautiful!
Help us help the user.
View: To focus, simplify, and customize the perception each user has of the database.