0

As you know SQL Server unlike MySQL has no Group_Concat function, so for this purpose how to write a query for SQL Server.

I created an application by using Visual Studio. Application has a Windows Form. I am able to save, delete and update records in the database table. I need to display a person's email addresses, so I need to use a similar method like Group_concat.

Can you help me about with this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
joshuacos2777
  • 7
  • 5
  • Could you post some sample data and expected result. – Felix Pamittan May 29 '17 at 21:40
  • A common way to do aggregate string concatenation in current SQL Server versions is with an XML PATH subquery. Include the items @FelixPamittan requested if you need help with that. SQL Server 2017, which will presumably be released later this year, includes a `STRING_AGG` function to facilitate this. – Dan Guzman May 29 '17 at 21:45
  • 1
    The question has been answered many times, there are several ways to do it - and all of them are slow. If you want speed, construct the string on client. – Antonín Lejsek May 29 '17 at 23:19

1 Answers1

1

You might need to use xml Path for SQL Server <= 2016 and String_Agg from SQL Server 2017, SQL Azure etc., Please find below the sample aggregation

CREATE TABLE dbo.[#Table1] (fullname VARCHAR(20),education VARCHAR(20))

INSERT INTO #Table1
SELECT 'john', 'harvard'    UNION ALL
SELECT 'john', 'mit'        UNION ALL
SELECT 'eric', 'mit'        UNION ALL
SELECT 'anna', 'yale'

--<=SQL Server 2016
Select fullname,  stuff ((
    select ',' + education from #Table1 where fullname = t.fullname 
    for xml path('')
    ),1,1,'') as education
From #Table1 t
Group by fullname

--For SQL Server 2017, SQL Azure, SQL PDW
Select fullname, string_agg(education,',') as Education from #table1
Group by fullname
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38