Concatenate Rows in SQL using Stuff and For XML Path

Table 1:

id            group               TIN              SSN
22      123456780      123456789      1104999999
22      123456790      123456789      1771828111
22      123456778      123456789      1333301423
22      123456745      123456789      1598884222
22      123456756      123456789      1511008000

I want the result to be like below

22               1104999999, 1771828111, 1333301423, 1598884222, 1511008000


So I wrote the below query...

SELECT DISTINCT tbl1.id,
 ssn_group =  STUFF((SELECT ', ' + tbl1.tx_NPI  AS [text()]
           FROM ssn_group_values tbl12
           WHERE tbl12.id = tbl1.id
          FOR XML PATH('')), 1, 1, '')
 FROM ssn_group_values tbl1
 WHERE tbl1.id = 22


but i did not get the expected results...instead i got below results...

id                      ssn_group
22      1104999999, 1104999999, 1104999999, 1104999999, 1104999999
22      1771828111, 1771828111, 1771828111, 1771828111, 1771828111
22      1333301423, 1333301423, 1333301423, 1333301423, 1333301423
22      1598884222, 1598884222, 1598884222, 1598884222, 1598884222
22      1511008000, 1511008000, 1511008000, 1511008000, 1511008000


So what is the mistake in my query ?
shragiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bhavesh ShahLead AnalysistCommented:
Hi,

checkout this sample one.

Declare @Aliases Table
(
	[FirstName] varchar(50) not null,
	[LastName] varchar(50) not null,
	[Alias] varchar(100) not null
)

Insert @Aliases
Select 'Clark','Kent','Superman'
Union All
Select 'Clark','Kent','Kal-El'
Union All
Select 'Clark','Kent','Gangbuster'
Union All
Select 'Clark','Kent','Supernova'
Union All
Select 'Clark','Kent','Nightwing'
Union All
Select 'Peter','Parker','Spiderman'
Union all
Select 'Peter','Parker','WebSligner'

Select * from @Aliases

Select Distinct
	[LastName],
	[FirstName],
	(Stuff((Select ', ' + Alias From @Aliases T2 Where T2.FirstName = T1.FirstName and T2.LastName = T2.LastName FOR XML PATH('')),1,2,'')) as Aliases
From @Aliases T1
Order By [LastName], [FirstName]

Open in new window

0
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT DISTINCT tbl1.id,
 ssn_group =  STUFF((SELECT ', ' + tbl1.tx_NPI  AS [text()]
           FROM (SELECT DISTINCT id, ssn_group_values tbl12 ) tbl12
           WHERE tbl12.id = tbl1.id
          FOR XML PATH('')), 1, 1, '')
 FROM ssn_group_values tbl1
 WHERE tbl1.id = 22
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shragiAuthor Commented:
Actually i made a simple mistake in my query...
In the below query ... (in stuff sub query) it should be tbl12.tx_npi but
i wrote tbl1.tx_NPI ...that is the mistake...
when i fiexd that it worked... but thanks for all the responses...



SELECT DISTINCT tbl1.id,
 ssn_group =  STUFF((SELECT ', ' + tbl12.tx_NPI  AS [text()]
           FROM ssn_group_values tbl12
           WHERE tbl12.id = tbl1.id
          FOR XML PATH('')), 1, 1, '')
 FROM ssn_group_values tbl1
 WHERE tbl1.id = 22


Thanks,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.