Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    19

    Red face Unanswered: Pivot one more than one column...

    Stuck on trying to pivot on four columns, I have code that works one one column but can't get it to work for more than one... any ideas much apreciated.


    Here's my code which works for one column:

    DECLARE @query VARCHAR(4000)
    DECLARE @prodtype VARCHAR(2000)
    SELECT @prodtype = STUFF(( SELECT DISTINCT
    '],[' + ltrim(([Product Type]))
    FROM ProductCosts
    ORDER BY '],[' + ltrim(([Product Type]))
    FOR XML PATH('')
    ), 1, 2, '') + ']'

    SET @query =

    'SELECT * FROM
    (
    SELECT [ProductID], [Product Type], [Product Value]
    FROM ProductCosts
    )t

    PIVOT (SUM([Product Value]) FOR [Product Type]
    IN ('+@prodtype+')) AS pvt1
    '

    EXECUTE (@query)
    Attached Thumbnails Attached Thumbnails Test_Pivot.JPG  

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Pivot operations are best handled on the app server (for N-tier applications) or the client (on 2-tier applications).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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