0

I have 1 table called BSFolks that contains:

Name         Number
----         ------
Sam Jackson  BS001
Sam Jackson  BS003
Sam Jackson  null
Miley Cyrus  BS666
Miley Cyrus  BS069
Miley Cyrus  BS013
C. Agulera   BS420

And would like to return

Name          Number
----          ------
Sam Jackson   BS001,BS003
Miley Cyrus   BS666,BS069,BS013
C. Agulera    BS420

I've been having a hard time finding the correct search terms for this operation so please excuse me if this has already been answered.

I am using SQL Server 2008 BTW.

Thanks y'all!

EDIT:

Here's the solution.

SELECT Name, STUFF((SELECT DISTINCT ',' + Number FROM BSFolks 
WHERE Name = X.Name 
AND (Number != '' AND Number IS NOT NULL) 
FOR XML PATH ('')), 1, 1, '') AS Numbers
FROM BSFolks X
GROUP BY Name
rimraf
  • 3,925
  • 3
  • 25
  • 55
  • https://www.microsoft.com/en-us/sql-server/sql-server-2008 - upgrade your instance to Azure/SQL Server 2017 and just use `STRING_AGG` function :) – Lukasz Szozda Jun 13 '19 at 19:31
  • Otherwise you are searching for: [Simulating group_concat MySQL function in Microsoft SQL Server](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – Lukasz Szozda Jun 13 '19 at 19:32
  • Oh geeze, thats convoluted! I'm having a hard time translating that! Thanks for the link though. I will grope at it. I wish i could upgrade it. Unfortunately that's not my dept... sigh. I know they are planning to use 2018 here soon. – rimraf Jun 13 '19 at 19:50

1 Answers1

0
SELECT Name, STUFF((SELECT DISTINCT ',' + Number FROM BSFolks 
WHERE Name = X.Name 
AND (Number != '' AND Number IS NOT NULL) 
FOR XML PATH ('')), 1, 1, '') AS Numbers
FROM BSFolks X
GROUP BY Name
rimraf
  • 3,925
  • 3
  • 25
  • 55
  • I’m honestly not entirely sure what xml path is doing to be honest. I will update once I do some reading on the matter though. – rimraf Jun 14 '19 at 01:22