How to add comma separated strings in sql

Question

I have 2 tables as following

---------------------------------
| PId |           uniid          |
|-----|------------------------  |
|   1 |      xxxggsgsg           |
|   3 |     xxxxxggsgs           |
|   4 |     xxxxxggsgsg          |
|   5 |         gfgjsfgjf        |
|   6 |      gsgjsfgjf           |
|   7 |        gfgjsfgjf         |
----------------------------------

---------------------------------------
| PId |                email          |
|-----|-------------------------------|
|   4 |          [email protected]            |
|   6 | [email protected]                     |
|   7 | [email protected]                     |
|   9 | [email protected]                     |
|   1 | [email protected]                     |
|  22 | [email protected]                     |
|   1 | [email protected]                     |
---------------------------------------

And I want to display the uniid of the matched ids in both the tables and my query was

select email,count(email) as EmailCount  , (STUFF((SELECT CAST(', ' + t1.uniid AS VARCHAR(MAX)) 
         FROM t1 
         join t2
         on t1.PId = t2.PId
         group by t1.uniid                                      
         FOR XML PATH ('')), 1, 2, '')) AS uni
from t1
inner join t2
on t1.PId = t2.PId
group by email

And my output is

+------------------------------------------------------------------------------------------------------+
|       email          | EmailCount   |            uniid            |
--------------------------------------------------------------------------------------------------------
|    [email protected]   |  1        |           gfgjsfgjf , gsgjsfgjf , xxxxxggsgsg          |
--------------------------------------------------------------------------------------------------------
|[email protected]       |  1        |          gfgjsfgjf , gsgjsfgjf , xxxxxggsgsg          |
-------------------------------------------------------------------------------------------------------
|[email protected]        |3         |            gfgjsfgjf , gsgjsfgjf , xxxxxggsgsg        |
+-----------------------------------------------------------------------------------------------------+

Here the column is showing 3 even when the 3 uniids when the count is even. How can I show only the uniid for the matched pid. My sql fiddle is Sample DB

So you want the email address, number of emails for that address and the uniid corresponding to the mail address? There is only one pid per mail address or can there be multiple pid values for one given mail address which the column name "skus" implies? — maxhb
– maxhb, Commented Nov 16, 2015 at 10:00

Lukasz Szozda · Accepted Answer · 2015-11-16 10:14:44Z

2

You can use CTE to do join first, and then in main query use correlated subquery to generate comma separated list:

WITH cte AS
(
  SELECT email, uniid
  FROM #t1 t1
  JOIN #t2 t2
    ON t1.PId = t2.PId
)
SELECT DISTINCT email, 
     [EmailCount] = COUNT(*) OVER (PARTITION BY email),
     [skus]       = (STUFF((SELECT CAST(', ' + uniid AS VARCHAR(MAX)) 
                            FROM cte   c2  
                            WHERE c2.email = c1.email
                            FOR XML PATH ('')), 1, 2, ''))
FROM cte c1;

LiveDemo

Output:

╔═══════════╦════════════╦══════════════════════════════════════════╗
║   Email   ║ EmailCount ║                   Skus                   ║
╠═══════════╬════════════╬══════════════════════════════════════════╣
║ [email protected] ║          1 ║ gsgjsfgjf                                ║
║ [email protected] ║          1 ║ gfgjsfgjf                                ║
║ [email protected] ║          3 ║ xxxxxggsgsg , xxxxxggsgsg , xxxxxggsgsg  ║
╚═══════════╩════════════╩══════════════════════════════════════════╝

edited Nov 16, 2015 at 10:14

answered Nov 16, 2015 at 10:01

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ha chitti Over a year ago

Thanks mate,can't I do with my existing query @lad2025

Lukasz Szozda Over a year ago

@hachitti I don't understand you. This is based on your query and data just remove # from table names. SEDE does not allow to create normal tables so I've created temporary tables.

Collectives™ on Stack Overflow

How to add comma separated strings in sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related