Post reply

Help with Query

  • November 4, 2008 at 5:19 pm

    #195653

    Please help me with this query. Sample data:

    create table #BeforeTraining (ID int identity, SalePerson varchar (10), Cat varchar (10), Qty int)

    insert #BeforeTraining (SalePerson, Cat, Qty)

    select 'A', 'Supplies' , '5'

    union select 'A', 'Hardware' , '6'

    union select 'B', 'Supplies' , '10'

    union select 'C', 'Hardware' , '20'

    create table #AfterTraining (ID int identity, SalePerson varchar (10), Cat varchar (10), Qty int)

    insert #AfterTraining (SalePerson, Cat, Qty)

    select 'A', 'Supplies' , '8'

    union all select 'A', 'Garden' , '8'

    union all select 'B', 'Supplies' , '8'

    union all select 'B', 'Garden' , '11'

    union all select 'C', 'Supplies' , '10'

    Managers want to see how much people improved after a traning so I need to group it by SalePerson then Category as the result of the query below:

    select 'A' as SalePerson,'Hardware' as Cat1,'6' as Qty1, null as Cat2, null as Qty2

    union all

    select 'A','Supplies', '5', 'Supplies', '8'

    union all

    select 'A', null, null, 'Garden', '8'

    union all

    select 'B', 'Supplies', '10', 'Supplies', '8'

    union all

    select 'B', null, null, 'Garden', '11'

    union all

    select 'C', 'Hardware', 20, null, null

    union all

    select 'C', null, null, 'Supplies', '10'

    I can't find a way using a join to get the result. Hope you can help me with this.

    Regards,

  • November 4, 2008 at 5:37 pm

    #893973

    Table definitions and expected output please.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 4, 2008 at 5:41 pm

    #893980

    Table definitions is in the sample data. I am just looking for a query to produce a result that look like the result when you run the second query. Thanks.

  • November 4, 2008 at 5:54 pm

    #893987

    Try this:

    Select b.SalePerson

    , b.Cat

    , a.SalePerson

    , a.Cat

    From #BeforeTraining b

    full outer join #AfterTraining a

    ON b.SalePerson = a.SalePerson

    And b.Cat = a.Cat

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • November 4, 2008 at 6:27 pm

    #893990

    WOW!!!!!

    That is exactly what I am looking for. Thanks a million.

  • November 4, 2008 at 6:33 pm

    #893991

    htt (11/4/2008)

    Table definitions is in the sample data. I am just looking for a query to produce a result that look like the result when you run the second query. Thanks.

    Sorry. I misread it and didn't notice the create table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 4, 2008 at 6:34 pm

    #893992

    Glad we could help!

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

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

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