May 6, 2013 at 7:26 pm
Hi all SQL gurus...
I need help in creating a query without any funtion to do merge owner's names from multiple row for a property, however when it has the same surname, then it should only show the surname once.
as the table is quite big, I think it's best to use result set instead of running function for each property to get the owner's name.
I've used the for xml path to join it, but doesn't remove the multiple username.
e.g. "Mr TK Wicki & Mrs NE Wicki" instead of "Mr TK & Mrs NE Wicki"
Sample data as below:
declare @temp table (propkey varchar(15), Title varchar(10), Initial varchar(10), Surname varchar(50))
insert into @temp (propkey, title, initial, surname)
values ('298213','Mr','M','McArthur'),
('298213','Mrs','D','McArthur'),
('301869','Mr','TK','Wicki'),
('301869','Mrs','NE','Wicki'),
('309048','Mr','RG','Thompson'),
('309048','Mr','DJ','Thompson'),
('309048','Ms','CJ','Cain'),
('309048','Ms','AJ','Cain'),
('357308','Mr','JD','Homer'),
('357308','Mrs','PG','Homer'),
('357308','Ms','ML','Homer'),
('378699','Mr','VB','Prince'),
('378699','Mrs','KV','Prince')
select * from @temp
Results needed:
'298213','Mr M & Mrs D McArthur'
'301869','Mr TK & Mrs NE Wicki'
'309048','Mr RG & Mr DJ Thompson & Ms CJ & Ms AJ Cain '
'357308','Mr JD & Mrs PG & Ms ML Homer'
'378699','Mr VB & Mrs KV Prince'
May 6, 2013 at 8:23 pm
Hi, I've found the solution using multiple WITH. I'm not sure if this is the most efficient one, but so far, it's running quite well.
I'm still open for suggestion for improvements... cheers...
use pccsde
go
declare @temp table (propkey varchar(15), Title varchar(10), Initial varchar(10), Surname varchar(50))
insert into @temp (propkey, title, initial, surname)
values ('298213','Mr','M','McArthur'),
('298213','Mrs','D','McArthur'),
('301869','Mr','TK','Wicki'),
('301869','Mrs','NE','Wicki'),
('309048','Mr','RG','Thompson'),
('309048','Mr','DJ','Thompson'),
('309048','Ms','CJ','Cain'),
('309048','Ms','AJ','Cain'),
('357308','Mr','JD','Homer'),
('357308','Mrs','PG','Homer'),
('357308','Ms','ML','Homer'),
('378699','Mr','VB','Prince'),
('378699','Mrs','KV','Prince'),
('478699','Mrs','AB','ABC')
;with prefnameDouble as (
select propkey, surname--, count(1) dCount
from @temp
group by propkey, surname
having count(1) > 1
)
, prefnameJoined as (
select distinct so.propkey
,PreferedName = REPLACE(
(SELECT SUBSTRING(
--(SELECT ' '+char(38)+' ' + ISNULL(ltrim(rtrim([title])) + ' ' + ltrim(rtrim(ISNULL([initial],''))) + ' ' + ltrim(rtrim(ISNULL([surname],''))), 'test')
(SELECT ' '+char(38)+' ' + ISNULL(ltrim(rtrim([title])) + ' ' + ltrim(rtrim(ISNULL([initial],''))) , 'test')
FROM @temp s
WHERE s.propkey = so.propkey and s.surname = so.surname
--ORDER BY s.Name
FOR XML PATH('')),7,200000))
,' &', ' &')
+ ' ' + coalesce(so2.surname, so.surname)
from @temp so
left join prefnameDouble so2 on so.propkey = so2.propkey and so.surname = so2.surname
)
select distinct
so.propkey
,PreferedName = REPLACE(
(SELECT SUBSTRING(
(SELECT ' '+char(38)+' ' + ISNULL([PreferedName], 'test')
FROM prefnameJoined s
WHERE s.propkey = so.propkey --and s.tpklpaaddr = so.tpklpaaddr
--ORDER BY s.Name
FOR XML PATH('')),7,200000))
,' &', ' &')
from @temp so
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply