Listing Duplicate Entries 1

[IanWaterman]

MS SQL Server 2008

Within a larger query I am looking up an Introducer, in rare cases a client has two introducers and this is duplicating the rest of the data.

I could modify my query to only bring back 1 introducer, however, I would like to look at the possibility of listing the Introducers

eg Current Result
Client Introducer
Client1 Introducer1
Client1 Introducer2
Client2 Introducer1

Would like to generate two columns
Client Introducers
Client1 Introducer1, Introducer2
Client2 Introducer1

Is this possible, if easier to do without a comma then a simple space will be sufficient

Thanks

Ian

 

[gmmastros]

try this:

Declare @Temp Table(Client VarChar(20), Introducer VarChar(20))

Insert Into @Temp Values('Client1', 'Introducer1')
Insert Into @Temp Values('Client1', 'Introducer2')
Insert Into @Temp Values('Client2', 'Introducer1')

select distinct Client,
  STUFF(
         (SELECT ', ' + convert(varchar(10), Introducer, 120)
          FROM @Temp t2
          where t1.Client = t2.Client
          FOR XML PATH (''))
          , 1, 1, '')  AS date
from @Temp t1;

This code puts your sample data into a table variable so that you can see how the query works. Once you are satisfied with it, you will need to integrate this in to your final query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

George

Brilliant answrv as usual , this was my fianl query

With
Introducers AS
(
select AD.ID AgreementID, AD.ClientNumber, AD.AgreementNumber, AD.LegalName ClientName,
AAD.LegalName IntroducerName
from D_AgreementDetails AD
inner join S_AgentAgreementSA AA
ON AD.Id = AA.SERVICEAGREEMENTID
--and (@AgreementID = 0 or AA.SERVICEAGREEMENTID = @AgreementID)
and Aa.DELETEDBYUSERID is null
and AA.ENDDATE is null

inner join D_AgreementDetails AAD
on AA.AGENTAGREEMENTID = AAD.ID
and AAD.ObjectClass = 'AgentAgreement'
)

select distinct AgreementID, ClientNumber, AgreementNumber, ClientName,
STUFF(
(SELECT ', ' + convert(varchar(100), IntroducerName, 120)
FROM Introducers t2
where t1.AgreementID = t2.AgreementID
FOR XML PATH (''))
, 1, 1, '') AS date
from Introducers t1;

Thank you

Ian

 

[gmmastros]

You're welcom

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom