SQL : Concatenate unique values in rows based on a condition.

Question

Post reply

SQL : Concatenate unique values in rows based on a condition.

gideon.e

SSC Veteran

Points: 273

February 14, 2019 at 2:11 am

#377017

I have the following code to concatenate all distinct Aliasnaam per Masjien.
Snippet


SnippetSELECT [Masjien], aliasname = STUFF((    
  SELECT N', ' + Aliasnaam FROM Oesskattings    
     WHERE [Masjien] = x.[Masjien]    
     FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
  FROM Oesskattings AS x
GROUP BY [Masjien]
ORDER BY [Masjien];


However my Aliasnaam is NOT distinct and is added in the concatenation for every row.
I have tried, but cannot figure where to make Aliasnaam distinct.

Regards

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 1

gideon.e - Thursday, February 14, 2019 2:11 AM

I have the following code to concatenate all distinct Aliasnaam per Masjien.
Snippet


SnippetSELECT [Masjien], aliasname = STUFF((    
  SELECT N', ' + Aliasnaam FROM Oesskattings    
     WHERE [Masjien] = x.[Masjien]    
     FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
  FROM Oesskattings AS x
GROUP BY [Masjien]
ORDER BY [Masjien];


However my Aliasnaam is NOT distinct and is added in the concatenation for every row.
I have tried, but cannot figure where to make Aliasnaam distinct.

Regards

Kindly provide sample data with create and insert statement.

Saravanan

gideon.e SSC Veteran Points: 273 More actions · Answer 2

Hi Saravan. Thank you. Icannot figure out how to do that in Visual Studio Community 2015 with a local .mdf database.

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 3

Does it work if you put a DISTINCT in the SQL?
SELECT [Masjien], aliasname = STUFF(( SELECT DISTINCT N', ' + Aliasnaam FROM Oesskattings WHERE [Masjien] = x.[Masjien] FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') FROM Oesskattings AS x GROUP BY [Masjien] ORDER BY [Masjien];

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

gideon.e - Thursday, February 14, 2019 5:55 AM
Hi Saravan. Thank you. Icannot figure out how to do that in Visual Studio Community 2015 with a local .mdf database.

Please see the article at the first link under "Helpful Links" in my signature line below for one way to do such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gideon.e SSC Veteran Points: 273 More actions · Answer 5

Jonathan AC Roberts - Thursday, February 14, 2019 6:12 AM
Does it work if you put a DISTINCT in the SQL?
SELECT [Masjien], aliasname = STUFF(( SELECT DISTINCT N', ' + Aliasnaam FROM Oesskattings WHERE [Masjien] = x.[Masjien] FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') FROM Oesskattings AS x GROUP BY [Masjien] ORDER BY [Masjien];

Thank you Jonathan. Perfect.

gideon.e SSC Veteran Points: 273 More actions · Answer 6

Jeff Moden - Thursday, February 14, 2019 7:51 AM
gideon.e - Thursday, February 14, 2019 5:55 AM
Hi Saravan. Thank you. Icannot figure out how to do that in Visual Studio Community 2015 with a local .mdf database.
Please see the article at the first link under "Helpful Links" in my signature line below for one way to do such a thing.

Thank you Jeff. I will check it out and use in the future.