Results 1 to 3 of 3

Thread: Dynamic PIVOT

  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: Dynamic PIVOT

    How do I dynamically pivot a table? On my example below, the STORE changes.

    TIA
    Attached Thumbnails Attached Thumbnails Stores.PNG  

  2. #2
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by ARPRINCE View Post
    How do I dynamically pivot a table? On my example below, the STORE changes.TIA
    OK I did some research and this is what I got:


    DECLARE @PivotColumnHeaders VARCHAR(MAX)
    SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + [STORE] + ']',
    '[' + [STORE] + ']'
    )
    FROM PIVOTME ORDER BY STORE


    DECLARE @PivotTableSQL NVARCHAR(MAX)
    SET @PivotTableSQL = N'SELECT *
    FROM (
    SELECT
    HD,
    [STORE],
    CNT
    FROM PIVOTME
    ) TableDate
    PIVOT (
    SUM(CNT)
    FOR [STORE] IN (
    ' + @PivotColumnHeaders + '
    )
    ) PivotTable'

    EXECUTE(@PivotTableSQL)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The better question is WHY would you want to dynamically pivot data in SQL?
    It limits its use by reporting tools and other interfaces, as the layout is not static.
    You should output your data as a flat data set, and do the pivoting in your reporting layer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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