Need comma separated value in table in SQL Server 2014

Question

I have a table like this.

Create table #temp
(
    id int,
    firstname varchar(50),
    lastname varchar(50)
)

insert into #temp (id, firstname, lastname)
select 1,'mit','jain'

insert into #temp (id, firstname, lastname)
select 1,'mit','jain1'

insert into #temp (id, firstname, lastname)
select 1,'mit','jain2'

insert into #temp (id, firstname, lastname)
select 2,'mit','jain3'

insert into #temp (id, firstname, lastname)
select 2,'mit','jain4'

insert into #temp (id, firstname, lastname)
select 1,'mit','jain5'

insert into #temp (id, firstname, lastname)
select 1,'mit','jain6'

I want the table to be shown as below

id  firstname  lastname
----------------------------------------------
 1   mit        jain,jain1,jain2,jain5,jain6 
 2   mit        jain2,jain4

I have tried the query as below

select  
    id, firstname,
    substring((Select ', '+tc1.lastname AS [text()]
               From #temp tc1
               Inner Join #temp c1 On c1.id = tc1.id
               Where tc1.firstname = c1.firstname
               Order BY tc1.lastname
               For Xml Path('')), 2, 1000) 'LastName1' 
from #temp 
group by id, firstname

But it's not working. Please help me out

score 3 · Accepted Answer · answered Aug 08 '18 at 10:20

3

You're part of the way there. The tradition method is using STUFF:

SELECT t.id, t.firstname,
       STUFF((SELECT ', ' + sq.lastname
              FROM #temp sq
              WHERE sq.id = t.id
                AND sq.firstname = t.firstname
              ORDER BY sq.lastname
              FOR XML PATH('')),1,1,'') AS lastname
FROM #temp t
GROUP BY t.id, t.firstname;

There are lots of answers on SO already on how to do this though, but you have shown effort. :)

answered Aug 08 '18 at 10:20

Thom A

88,727
11
45
75

It can not be done in case of inner join? – Mitesh Jain Aug 08 '18 at 10:26
@MiteshJain no, you can't `INNER JOIN` to an expression and reference the external objects. I *suppose* you could use `CROSS APPLY`, however, this method is by far the more tradition method. Microsoft introduced a function in SQL Server 2017 (`STRING_AGG`), and that is also usedin the `SELECT`, not in the `FROM`. Within the `SELECT` is really the right place to do this, as it's really a aggregation. – Thom A Aug 08 '18 at 10:29
@Larnu IMHO showing effort is expected when asking a question in SO, so that shouldn't be a reason not to close a question as a duplicate. If you think the question is good, you can always upvote it. In fact, you can both upvote and vote to close (as I just did). Being a duplicate does not mean being a bad question. – Zohar Peled Aug 08 '18 at 11:38
@ZoharPeled I totally agree, it's expected, the problem is, far too often the person writting the question doesn't provide what they've tried (or they simple haven't). That's why I was more than happy to answer the OP here, rather than vote as a duplicate. The DDL and `INSERT` statement were also great additions. :) – Thom A Aug 08 '18 at 11:39
That's why I've upvoted the question - it's a well formatted SQL question, using DDL+DML for sample data, showing desired results and current attempt. If it wasn't a duplicate, I would say it's about as good as an SQL question can get (at least, as far as formatting and relevant data goes). – Zohar Peled Aug 08 '18 at 11:43

Need comma separated value in table in SQL Server 2014

1 Answers1