Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    16

    Unanswered: Pivot Table Question

    Hi guys,

    Hope you are all well.

    I have a pivot table problem that I'm hoping someone can help me with.

    If I have a record set like this:
    Code:
    ID   ProductID    IsActive
    --   ----------   --------
    1    10              True
    1    11              True
    1    12              False
    2    10              True
    2    11              False
    2    12              True
    3    10              False
    3    11              False
    3    12              False
    How would I go about turning it into a pivot table like this?

    Code:
    ID   ProductID10    ProductID11    ProductID12
    --   ------------   ------------    ------------
    1    True              True               False
    2    True              False              True
    3    False             False              False
    I've been at this for a while now without much luck, so any advice would be much appreciated.

    Thanks in advance,
    Krispy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Krispy:

    #1 stock answer - do it in front end!!!! (there's all sorts of reasons not to do it in SQL Server, not least because SQL is not well suited to this)

    #1 stock question (assuming you won't do above) - what version of SQL Server?
    #2 stock question (assuming you won't do above) - do you know the values of ProductID before running the query?
    #3 specific question - do you have to prefix the column headers with "ProductID"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2005
    Posts
    16
    Hi pootle - sorry, I should have mentioned I'm working with SQL 2005.

    To answer your questions:
    1) I would rather perform this on the SQL Server if possible.
    2) Yes, we could know the small number of values at design time, so I'm quite happy to go with the values supplied.
    3) No, I don't have to prefix the column headers with any text.

    Hope this helps.

  4. #4
    Join Date
    Nov 2005
    Posts
    16
    I think I worked it out.

    This was the original sql code that wasn't quite working:

    Code:
    SELECT ID, [10] AS 'ProductID10', [11] AS 'ProductID11', [12] AS 'ProductID12'
    FROM
          (SELECT ID, ProductID, IsActive
           FROM MyTable) AS SourceTable
    PIVOT
          (
          MAX(IsActive)
          FOR ProductID IN ([10], [11], [12])
          ) AS PivotTable
    ORDER BY ID
    ...I needed to change the IsActive boolean to a '1' or '0' using a conditional CASE statement as shown below.

    Code:
    SELECT ID, [10] AS 'ProductID10', [11] AS 'ProductID11', [12] AS 'ProductID12'
    FROM
          (SELECT ID, ProductID, CASE WHEN IsActive = 1 THEN 1 ELSE 0 END as active
           FROM MyTable) AS SourceTable
    PIVOT
          (
          MAX(active)
          FOR ProductID IN ([10], [11], [12])
          ) AS PivotTable
    ORDER BY ID
    Thanks again.

Posting Permissions

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