Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

MSFT: MADDOG - Pivot should allow dynamic column generation DCR

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'

0 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 11

    <=-=Dec 11 2007 1:26PM=-=>

    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

    <=-=Mar 10 2011 6:16PM=-=>

    Hello,

    I have resolved your request as duplicate of one below:

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


    Umachandar, SQL Programmability Team

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base