Results 1 to 8 of 8
  1. #1
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1

    Unanswered: Alternative way to PIVOT function

    Hi

    I need some help please with alternative code.

    We are running SQL2000, and I was trying to use the PIVOT function, as you are all aware 2000 does not support the PIVOT function.

    My question to you good folk, is there an alternative to the PIVOT function

    here my code I was trying to use

    Code:
    SELECT CostSavingsNo, CostSavingType
    
    From (Select CostSavingsNo, CostSavingType, SavingsValue FROM CostSavingsDetails) CSD
    
    PIVOT
    
    (SUM(SavingsValue) FOR CostSavingType IN ([1], [2], [3], [4], [5], [6], [7], [8],[9], [10], [11], [12]], [13], [14])) AS PVT
    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so is this a MySQL question or a Microsoft SQL Server question?
    if its is SQL Server let us know and it can be moved there.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Hi Healdem

    Sry its a Microsoft SQL Server question

  4. #4
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Solved -

    Used the case function, its probably not the most efficient but it works

    Code:
    SELECT CostSavingsNo, 
    
           SUM(CASE WHEN CostSavingType = 1
                    THEN SavingsValue ELSE 0 END) AS '1',
           SUM(CASE WHEN CostSavingType = 2
                    THEN SavingsValue ELSE 0 END) AS '2',
           SUM(CASE WHEN CostSavingType = 3
                    THEN SavingsValue ELSE 0 END) AS '3',
           SUM(CASE WHEN CostSavingType = 4
                    THEN SavingsValue ELSE 0 END) AS '4',
           SUM(CASE WHEN CostSavingType = 5
                    THEN SavingsValue ELSE 0 END) AS '5',
           SUM(CASE WHEN CostSavingType = 6
                    THEN SavingsValue ELSE 0 END) AS '6',
           SUM(CASE WHEN CostSavingType = 7
                    THEN SavingsValue ELSE 0 END) AS '7',
           SUM(CASE WHEN CostSavingType = 8
                    THEN SavingsValue ELSE 0 END) AS '8',
           SUM(CASE WHEN CostSavingType = 9
                    THEN SavingsValue ELSE 0 END) AS '9',
           SUM(CASE WHEN CostSavingType = 10
                    THEN SavingsValue ELSE 0 END) AS '10',
           SUM(CASE WHEN CostSavingType = 11
                    THEN SavingsValue ELSE 0 END) AS '11',
           SUM(CASE WHEN CostSavingType = 12
                    THEN SavingsValue ELSE 0 END) AS '12',
           SUM(CASE WHEN CostSavingType = 13
                    THEN SavingsValue ELSE 0 END) AS '13',
           SUM(CASE WHEN CostSavingType = 15
                    THEN SavingsValue ELSE 0 END) AS '15'
    
    FROM CostSavingsDetails
    
    GROUP BY CostSavingsNo;
    Thanks

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to point out another format of CASE expression.

    CASE (Transact-SQL)
    CASE (Transact-SQL)

    ...

    The CASE expression has two formats:

    • The simple CASE expression compares an expression to a set of simple expressions to determine the result.
    • The searched CASE expression evaluates a set of Boolean expressions to determine the result.

    ...
    ...

    Syntax

    Code:
    Simple CASE expression: 
    CASE input_expression 
         WHEN when_expression THEN result_expression [ ...n ] 
         [ ELSE else_result_expression ] 
    END 
    ...

    An example used the simple CASE format might be...
    Code:
    SELECT
           CostSavingsNo
         , SUM(CASE CostSavingType
               WHEN  1 THEN SavingsValue ELSE 0 END) AS '1'
         , SUM(CASE CostSavingType
               WHEN  2 THEN SavingsValue ELSE 0 END) AS '2'
         , SUM(CASE CostSavingType
               WHEN  3 THEN SavingsValue ELSE 0 END) AS '3'
         , SUM(CASE CostSavingType
               WHEN  4 THEN SavingsValue ELSE 0 END) AS '4'
         , SUM(CASE CostSavingType
               WHEN  5 THEN SavingsValue ELSE 0 END) AS '5'
         , SUM(CASE CostSavingType
               WHEN  6 THEN SavingsValue ELSE 0 END) AS '6'
         , SUM(CASE CostSavingTyp
               WHEN  7 THEN SavingsValue ELSE 0 END) AS '7'
         , SUM(CASE CostSavingType
               WHEN  8 THEN SavingsValue ELSE 0 END) AS '8'
         , SUM(CASE CostSavingType
               WHEN  9 THEN SavingsValue ELSE 0 END) AS '9'
         , SUM(CASE CostSavingType
               WHEN 10 THEN SavingsValue ELSE 0 END) AS '10'
         , SUM(CASE CostSavingType
               WHEN 11 THEN SavingsValue ELSE 0 END) AS '11'
         , SUM(CASE CostSavingType
               WHEN 12 THEN SavingsValue ELSE 0 END) AS '12'
         , SUM(CASE CostSavingType
               WHEN 13 THEN SavingsValue ELSE 0 END) AS '13'
         , SUM(CASE CostSavingType
               WHEN 15 THEN SavingsValue ELSE 0 END) AS '15'
     FROM
           CostSavingsDetails
     GROUP BY
           CostSavingsNo
    Last edited by tonkuma; 01-14-14 at 09:06.

  6. #6
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Thanks for the update tonkuma

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not seeing that Tonkuma proposed anything different than MarkWhyte.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to point out another format of CASE expression.
    MarkWhyte: The searched CASE expression
    Code:
           SUM(CASE WHEN CostSavingType = 1
                    THEN SavingsValue ELSE 0 END) AS '1'
    Tonkuma: The simple CASE expression
    Code:
           SUM(CASE CostSavingType
               WHEN  1 THEN SavingsValue ELSE 0 END) AS '1'

Posting Permissions

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