Help with SQL Query

  • Hi,

    i got a table which resembles like

    IdTypeAddressCity

    1Homeabcca

    1Officexyzal

    1Workrjfmi

    2Homeefgoh

    2Workopamn

    3Homestyca

    Query for the table

    create table sample

    (

    Id int not null,

    Type varchar(10),

    Address varchar(50),

    City varchar(5)

    )

    insert into sample values(1,'Home','abc','ca')

    insert into sample values(1,'Office','xyz','al')

    insert into sample values(1,'Work','rjf','mi')

    insert into sample values(2,'Home','efg','oh')

    insert into sample values(2,'Work','opa','mn')

    insert into sample values(3,'Home','sty','ca')

    select * from sample

    I'm trying to write a query whihc produces result like

    Id---HAdd---Hcity---oadd---ocity---wadd---wcity

    1 ---- abc ---- ca ---- xyz ---- al ---- rjf ---- mi

    2 ---- efg ---- oh ---- Null ---- Null ---- opa --- mn

    3 ---- sty ---- ca---- Null ---- NUll ---- Null ---- Null

    Can some one help me out with this please

  • You are going to have to join this table back to itself. This is pretty basic. give it a try and post back what you tried. Yes, I could write the sql in about a minute but you wouldn't learn anything by simply copying and pasting.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • You could also have a look at the CrossTab article referenced in my signature.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Sean Lange (8/30/2011)


    You are going to have to join this table back to itself. This is pretty basic. give it a try and post back what you tried. Yes, I could write the sql in about a minute but you wouldn't learn anything by simply copying and pasting.

    Join the table back to itself? Why would you do that? A cross-tab will do this easily and fast. OP provided us with ddl and test data, so I think he has earned the solution:

    select s.ID,

    max(case s.Type when 'Home' then s.Address end) as HAdd,

    max(case s.Type when 'Home' then s.City end) as Hcity,

    max(case s.Type when 'Office' then s.Address end) as oadd,

    max(case s.Type when 'Office' then s.City end) as ocity,

    max(case s.Type when 'Work' then s.Address end) as wadd,

    max(case s.Type when 'Work' then s.City end) as wcity

    from sample s

    group by s.ID



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • Hey,

    I tried it but couldn't get the logic for that.

    I tried to use case statement but wasn't successful with that as it will be helpful for returning single cloumn.

    I'm not able to get the logic for that self join may be due to my poor coding skills 🙁 Can you let me know the logic so that i can try it out from there.

  • Excellent point!!

    R.P.Rozema (8/30/2011)


    OP provided us with ddl and test data, so I think he has earned the solution:

    The cross tab will outperform the self join but here is how you would do the self join (and slower) way. 😛

    select sh.ID, sh.Address, sh.City, so.Address as oadd, so.City as ocity, sw.Address as wadd, sw.City as wcity

    from sample sh

    left join sample so on so.ID = sh.ID and so.Type = 'Office'

    left join sample sw on sw.ID = sh.ID and sw.Type = 'Work'

    where sh.Type = 'Home'

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • The following approach might cover for some missing Home Addrees values, too (that would be eliminated by the LEFT OUTER JOIN approach).

    But the CrossTab method posted by R.P.Rozema is definitely the way to go!

    SELECT

    COALESCE(s1.id,s2.id,s3.id) AS id,

    MAX(s1.ADDRESS) AS HAddr,

    MAX(s1.City) AS Hcity,

    MAX(s2.ADDRESS) AS oAddr,

    MAX(s2.City) AS ocity,

    MAX(s3.ADDRESS) AS wAddr,

    MAX(s3.City) AS wcity

    FROM

    (SELECT * FROM SAMPLE WHERE type ='Home') s1

    FULL OUTER JOIN

    (SELECT * FROM SAMPLE WHERE type ='Office') s2 ON s1.id=s2.id

    FULL OUTER JOIN

    (SELECT * FROM SAMPLE WHERE type ='Work') s3 on s1.id=s3.id

    GROUP BY COALESCE(s1.id,s2.id,s3.id)

    Edit: added the COALESCE function to get all id values if there's a home addres missing. But that does also require a MAX() GROUP BY.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • @r.p,@sean,@lutz: thank you so much guys..

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

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