August 30, 2011 at 11:36 am
Hi,
i got a table which resembles like
Id Type Address City
1 Home abc ca
1 Office xyz al
1 Work rjf mi
2 Home efg oh
2 Work opa mn
3 Home sty ca
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
August 30, 2011 at 12:35 pm
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/
August 30, 2011 at 2:25 pm
You could also have a look at the CrossTab article referenced in my signature.
August 30, 2011 at 2:28 pm
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
August 30, 2011 at 2:31 pm
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.
August 30, 2011 at 2:39 pm
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/
August 30, 2011 at 2:45 pm
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.
August 30, 2011 at 3:20 pm
@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