USing CTE t get Data but using stuff not working

Question

I have a very big query with multiple joins, i am some records but they are duplicated, there 3 columns which having different data

so i am trying to merge those all 3 in one row, with the values as comma separated

I used the CTE to get the table as:

;with cte(id,name,email,roles,country) as( select from mytable 4 joins and where clauses with data passed to it));

now i am doing this

select id,name,stuff((select email + ',' from cte FOR XML PATH ('')), 1, 1, '')) from cte group by id,name

but it is populating all the other rows where the data does not belong...

i have the results like this

ID     name      email              roles     campus
1      User1     user@gmail.com       Admin      Egypt
2      User1     user@gmail.com       Moderator  Egypt 
3      User1     user@gmail.com       Guest      USA
4      User2     user2@gmail.com     User        Brazil
5      User2     user2@gmail.com     Admin      Cairo
6      User2     user2@gmail.com     Dummy      Namibia

and trying to build like this

ID     name      email              roles                      campus
1      User1     user@gmail.com     Admin,Moderator,Egypt      Egypt,Egypt,USA
2      User2     user1@gmail.com    User,Admin,Dummy           Brazil,Cairo,Namibia

SAmple data expect results, and your full attempt will help us help you here. — Thom A, Sep 01 '20 at 15:42
you need to provide some test data and the desired output. Your provided queries are not enough to help you with the issue. Also what version of SQL Server are you using? — M.Ali, Sep 01 '20 at 15:43
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ae4ba9e5b52fd85094f17d24c87fc32e — Asum, Sep 01 '20 at 15:48
updated fiddle https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=321570da9c61ad15093c4b80ba678305 — Asum, Sep 01 '20 at 15:54
That isn't a fiddle, @Asum , there's no DDL and DML in there. And that information shouldn't be in a fiddle any way; [edit] your question. — Thom A, Sep 01 '20 at 16:00
Your output doesn't make sense. How is ID 2 associated with user 2? You could use min(id) in your select and only group by name, but that will have id 4 associated with user 2, which is correct for your sample data. The reason you were getting one row per id was because every single row in your sample data is unique — Radagast, Sep 01 '20 at 19:13

score 0 · Answer 1 · answered Sep 01 '20 at 21:12

The stuff() function is not the problem here. The way you apply the for xml needs fixing. There are already many good examples on this site like this one.

Sample data

create table cte
(
  ID int,
  name nvarchar(10),
  email nvarchar(20),
  role nvarchar(10),
  campus nvarchar(10)
)

insert into cte (ID, name, email, role, campus) values
(1, 'User1', 'user@gmail.com',  'Admin',     'Egypt'  ),
(2, 'User1', 'user@gmail.com',  'Moderator', 'Egypt'  ),
(3, 'User1', 'user@gmail.com',  'Guest',     'USA'    ),
(4, 'User2', 'user2@gmail.com', 'User',      'Brazil' ),
(5, 'User2', 'user2@gmail.com', 'Admin',     'Cairo'  ),
(6, 'User2', 'user2@gmail.com', 'Dummy',     'Namibia');

Solution

I am completely ignoring the ID column here, those look like row numbers to me and not actual ID's (meaning there is no relation between 1, 2, 3 and User1).
I also did not bother with the email column, because those do seem to be related to the name.

This gives:

select row_number() over(order by c1.name) as ID,
       c1.name,
       c1.email,
       stuff((  SELECT ',' + Role
                FROM cte c3
                WHERE c3.name = c1.name
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,1,'') AS Roles,
       stuff((  SELECT ',' + Campus
                FROM cte c3
                WHERE c3.name = c1.name
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,1,'') AS Campuses
from cte c1
group by c1.name, c1.email
order by c1.name;

Result

ID  name   Emails           Roles                  Campuses
--- ------ ---------------- ---------------------- ---------------------
1   User1  user@gmail.com   Admin,Moderator,Guest  Egypt,Egypt,USA
2   User2  user2@gmail.com  User,Admin,Dummy       Brazil,Cairo,Namibia

Fiddle

but the above also takes into consideration the where clauses and the joins in the CTE already, i do need to specify that right — Asum, Sep 01 '20 at 22:02
I assumed your current results are the output of your CTE. I cannot work with "_select from mytable 4 joins and where clauses with data passed to it_" so I created a table named `cte` as my starting point. So yes, you will have to specify your CTE as `with cte as (...)` before my solution query. — Sander, Sep 02 '20 at 07:04
@Asum, your (additional) question is not clear to me, please [elaborate in chat](https://chat.stackoverflow.com/rooms/220864/room-for-sander-and-asum). — Sander, Sep 02 '20 at 13:39

USing CTE t get Data but using stuff not working

1 Answers1