how can i change the sql get the union result

Question

how can i change the select...

Student
id name home tel
1 A Neo 023**
2 A Cit 012**
3 B Dti 12**
4 .. ... ...

I have a select sql query like:

SELECT
    name,
    home,
    tel
FROM Student
WHERE id<4;

And the result was like↓
name home tel
A Neo 023**
A Cit 012**
B Dti 12**

But I want to get the result like
name home tel
A Neo,Cit 023**,012**
B Dti 12**

Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Amira Bedhiafi, Jul 04 '19 at 14:41

score 0 · Answer 1 · answered Jul 04 '19 at 14:59

0

You could use a CURSOR or a CTE and concatenate the strings in the home and tel fields. See here for an example.

answered Jul 04 '19 at 14:59

Michael Tobisch

1,034
6
15

score 0 · Answer 2 · answered Jul 04 '19 at 15:46

try the following:

declare @Student table (id int, name varchar(10), home varchar(100), tel varchar(10))
insert into @Student select 1, 'A', 'Neo', '023**' 
insert into @Student select 2, 'A', 'Cit', '012**'
insert into @Student select 3, 'B', 'Dti', '12**'

select * from @Student

select name, stuff((
        select distinct ',' + u.home
        from @Student u
        where u.name = s.name
        for xml path('')),1,1,'') as home, stuff((
        select distinct ',' + u.tel
        from @Student u
        where u.name = s.name
        for xml path('')),1,1,'') as tel
from @Student s
group by name

how can i change the sql get the union result

2 Answers2