February 28, 2013 at 9:11 am
Hello All,
I have encountered a new problem that I haven't dealt with before. I have a table of individuals who have more than 1 address type. I would like to write a query that returns 1 address for each individual. This result set should include an address based on a pecking order. For this example, the pecking order should be Main > Work > Seasonal
Here's a sample table:
IndName Account AddressType AddressLine1 AddressLine2 City St
John 12345 Main 123 Main St. Boston MA
John 12345 Work 45 Spring St. Boston MA
John 12345 Seasonal 23 Ivaloo St. Portland ME
Jim 23456 Seasonal 14 Church St. Houghton ME
The output desired is:
IndName Account AddressType AddressLine1 AddressLine2 City St
John 12345 Main 123 Main St. Boston MA
Jim 23456 Seasonal 14 Church St. Houghton ME
Any thoughts on the most efficient way to approach this?
Thanks in advance!
-Ken G
February 28, 2013 at 9:29 am
I might have over-complicated this, I feel like I have but haven't really got the time to dig into it any further.
Using the following sample data: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT IndName,Account,AddressType,AddressLine1,AddressLine2,City,St
INTO #testEnvironment
FROM (VALUES('John',12345,'Main','123 Main St.','','Boston','MA'),
('John',12345,'Work','45 Spring St.','','Boston','MA'),
('John',12345,'Seasonal','23 Ivaloo St.','','Portland','ME'),
('Jim',23456,'Seasonal','14 Church St.','','Houghton','ME')
)a(IndName,Account,AddressType,AddressLine1,AddressLine2,City,St);
You could do this: -
SELECT IndName,Account,AddressType,AddressLine1,AddressLine2,City,St
FROM (SELECT IndName,Account,AddressType,AddressLine1,AddressLine2,City,St,
Pos,MIN(Pos) OVER(PARTITION BY Account)
FROM #testEnvironment
CROSS APPLY --== This would be better as a look-up table I think ==--
(SELECT Pos
FROM (VALUES('Main',1),('Work',2),('Seasonal',3)
)a(Pecking,Pos)
WHERE Pecking = AddressType AND Account = Account
)b
)a(IndName,Account,AddressType,AddressLine1,AddressLine2,City,St,Pos,MinPos)
WHERE Pos=MinPos;
Which produces: -
IndName Account AddressType AddressLine1 AddressLine2 City St
------- ----------- ----------- ------------- ------------ -------- ----
John 12345 Main 123 Main St. Boston MA
Jim 23456 Seasonal 14 Church St. Houghton ME
February 28, 2013 at 9:31 am
Something like this maybe?
;with cte
as
(
select IndName
, Account
, AddresType
, AddressLine1
, AddressLine2
, City
, ST
, ROW_NUMBER() over (partition by AddressType order by (case when AddressType = 'Main' then 1 when AddressType = 'Work' then 2 when AddressType = 'Seasonal' then 3 end)) as rownum
)
select * from cte where rownum = 1
February 28, 2013 at 9:43 am
I like the CTE approach that Erin posted. Just a couple changes to it so that it will produce the desired output.
;with cte
as
(
select IndName
, Account
, AddressType
, AddressLine1
, AddressLine2
, City
, ST
, ROW_NUMBER() over (partition by IndName order by (case when AddressType = 'Main' then 1 when AddressType = 'Work' then 2 when AddressType = 'Seasonal' then 3 end)) as rownum
from #testEnvironment
)
select * from cte where rownum = 1
Notice how Cadavre posted sample data. This is something you should do for future posts.
_______________________________________________________________
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/
February 28, 2013 at 9:45 am
Lol.. good point, Sean. I got side-tracked by a sendmail issue in my systems and posted before it was finished..
February 28, 2013 at 11:26 am
Thank you for this, and for putting together the sample code! Definitely helping me to learn a bit more!
February 28, 2013 at 1:30 pm
Something else I just learned... while trying to apply this to my actual database: if I leave out an addresstype in the "ROW_NUMBER() OVER () section, that row with the additional addresstype gets a rownum = 1. Just thought I'd mention it for others that might come across this. Thanks again for the help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply