Join columns with same address

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join columns with same address

Author

Topic

civicy2k
Starting Member

1 Post

Posted - 2010-11-15 : 09:11:22

Hi all,

I have data like below:

Member_ID Name Address City
001 Mr. A 123 St New York
002 Mr. B 123 St New York
003 Mr. C 123 St New York
004 Mr. D 123 St New York
005 Mr. E 456 St London
006 Mr. F 456 St London
007 Mr. G 456 St London

I need to create a mailing file and send only 1 letter to each household but address to all the name in that household:

Name Address City
Mr. A, Mr. B, Mr. C, Mr. D 123 St New York
Mr. E, Mr. F, Mr. G 456 St London

I tried to use self join but I have household with up to 11 members with the same address. How could I write my sql to create such a file?
Many thanks.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-15 : 09:17:39

WITH myCTE AS
(
SELECT
Member_ID
, Name
, Address
, City
, ROW_NUMBER() OVER(PARTITION BY Address, City ORDER BY Address, City) AS dupeNum
FROM
someTable
)

SELECT
*
FROM
myCTE
WHERE
(dupeNum = 1)
;

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-15 : 09:28:02

quote:
Originally posted by TimSman

WITH myCTE AS
(
SELECT
Member_ID
, Name
, Address
, City
, ROW_NUMBER() OVER(PARTITION BY Address, City ORDER BY Address, City) AS dupeNum
FROM
someTable
)

SELECT
*
FROM
myCTE
WHERE
(dupeNum = 1)
;

I didn't tried but i feel that it will return only Single person name. Ex: Mr. A instead of Mr. A, Mr. B, Mr. C, Mr. D.

Anyway I will check it once i get access to sql server

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-15 : 09:29:32

It will only return one name. I misread the part about grouping the names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources