Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2007

    Unanswered: PIVOT operator for variable number of transformations

    Hi, i'm trying to port a pivot query from access to sqlserver.
    I'm trying this query:

    SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
    [Department-3], [4] AS [Department-4]
    FROM (SELECT IDMerce, Pezzi, IDMagazzino
    FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
    IDMagazzino IN ([1], [2], [3], [4])) AS pvt

    this works, but in my case i don't know in advance how many transformations
    i need, so there is a solution?

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
    Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts