MSFT: MADDOG - Pivot should allow dynamic column generation DCR - by moody31415

Status : 

 


11
0
Sign in
to vote
ID 296108 Comments
Status Active Workarounds
Type Suggestion Repros 1
Opened 8/31/2007 10:25:52 AM
Access Restriction Public
Primary Feedback Item 127071

Description

Pivot is great, but I’d like to be able to pivot dynamically w/o having to do a whole lot of complicated stuff to figure out what I’m pivoting on… 
 
For example; today I have to do this to pivot on a column:
 
PROC [dbo].[PivotMachineOSes]
AS
    DECLARE @OSesString VARCHAR(MAX)
    SELECT @OSesString = ''
 
    SELECT @OSesString = @OSesString + '[' + CAST(OSID AS VARCHAR) + '],'
    FROM OSes
 
    -- trim the last comma from the string. 
    SELECT @OSesString = SUBSTRING(@OSesString, 0, LEN(@OSesString))
 
    EXEC('
    SELECT MachineID, ' + @OSesString + '
    FROM (
    SELECT m.MachineID, mo.OSID
    FROM dbo.Machines m WITH (NOLOCK)
    JOIN dbo.MachineOS mo WITH (NOLOCK) ON m.MachineID = mo.MachineID
    ) q
    PIVOT (COUNT(osID) FOR osID IN (' + @OSesString + ')) AS pvt
    ORDER BY MachineID')
GO
 
It would be so much nicer if I could instead say 
 
    SELECT MachineID, pvt.*
    FROM (
    SELECT m.MachineID, mo.OSID
    FROM dbo.Machines m WITH (NOLOCK)
    JOIN dbo.MachineOS mo WITH (NOLOCK) ON m.MachineID = mo.MachineID
    ) q
    PIVOT (COUNT(osID) FOR osID IN (select OSID from OSes)) AS pvt
    ORDER BY MachineID
 
The only reason my app goes through the trouble of figuring out the rows to pivot on is because it has to – if I could say ‘pivot on all the stuff in this column’ that’d be very helpful. 
 
This is an even better example: what are the columns that make up a statistic? 
 
select o.name, s.name, pvt.*
from sys.stats s
join sys.stats_columns sc on s.object_id = sc.object_id and s.stats_id = sc.stats_id
join sys.columns c on sc.object_id = c.object_id and sc.column_id = c.column_id
join sys.objects o on s.object_id = o.object_id
pivot(min(c.name) for stats_column_id in (*)) as pvt
where s.name like 'my_stat'
Sign in to post a comment.
Posted by Microsoft on 3/10/2011 at 6:16 PM
Hello,

I have resolved your request as duplicate of one below:

http://connect.microsoft.com/SQLServer/feedback/details/127071

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 12/11/2007 at 1:26 PM
Hello

Thank you for your feedback and for submitting your feature request to allow columns for PIVOT to be specified dynamically via a subquery. Technically, this request is difficult to fulfill because 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 accessed by the query. Having said that, we recognize that this is an important scenario and are already tracking this as a requested feature. We will continue to investigate alternate solutions for a future release of SQL Server.

Thank you.


SQL Server Engine Team