Post reply

Simple query help

  • December 6, 2009 at 9:39 pm

    #103862

    Hi,

    I have this table and rows

    CREATE TABLE #Temp (xType varchar(5), Name1 VARCHAR(15), Qty INT)

    GO

    Insert #Temp

    Select 'Dev','Test',12 union all

    Select 'Prod','Test',15 union all

    Select 'Dev','Test12',6 union all

    Select 'Prod','Test12',8 union all

    Select 'Dev','Test34',77 union all

    Select 'Prod','Test34',98 union all

    Select 'Dev','Test56',57 union all

    Select 'Prod','Test56',57

    GO

    I need to show the results like this.

    Name1DevProdDiff

    -----------------

    Test1215-3

    Test1268-2

    Test347798-21

    Test5657570

    Please help me with query. Thanks in advance.

  • December 6, 2009 at 10:17 pm

    #1088363

    Thank you for taking the time to post the test data the way you did... saves me a lot of time...

    This will do it...

    ;

    WITH

    ctePreAgg AS

    (

    SELECT Name1,

    SUM(CASE WHEN xType = 'Dev' THEN Qty ELSE 0 END) AS Dev,

    SUM(CASE WHEN xType = 'Prod' THEN Qty ELSE 0 END) AS Prod

    FROM #Temp

    GROUP BY Name1

    )

    SELECT Name1, Dev, Prod, Dev-Prod AS Diff

    FROM ctePreAgg

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 6, 2009 at 11:46 pm

    #1088379

    Thank you Jeff for the query. You saved my time. Thanks again.

  • December 8, 2009 at 12:51 pm

    #1089451

    SELECT Name1, [Dev], [Prod], [Dev] - [Prod] as 'Diff'

    FROM

    (

    SELECT xType, Name1, Qty

    FRoM #Temp

    ) as SrcTable

    PIVOT(

    SUM(Qty)

    FOR xType IN ( [Dev], [Prod] ) ) as PvtTbl

  • December 8, 2009 at 5:28 pm

    #1089594

    Yep... PIVOT will do... here's why I don't use it, though...

    http://qa.sqlservercentral.com/articles/T-SQL/63681/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 8, 2009 at 11:45 pm

    #1089661

    very good script practice question for beginners like me. I really love the answers.

  • December 9, 2009 at 9:59 am

    #1090001

    Jeff Moden (12/6/2009)

    Thank you for taking the time to post the test data the way you did... saves me a lot of time...

    This will do it...

    ;

    WITH

    ctePreAgg AS

    (

    SELECT Name1,

    SUM(CASE WHEN xType = 'Dev' THEN Qty ELSE 0 END) AS Dev,

    SUM(CASE WHEN xType = 'Prod' THEN Qty ELSE 0 END) AS Prod

    FROM #Temp

    GROUP BY Name1

    )

    SELECT Name1, Dev, Prod, Dev-Prod AS Diff

    FROM ctePreAgg

    Very cool script...

    _________________________________
    seth delconte
    http://sqlkeys.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply