Grouping the results of multiple rows in one column

Question

The query below gives me two results where everything is same except the last column v2_roles.id. Is there a way to group them in one column (may be a comma seprated list) and get just one result back

SELECT v2_admin.adminid, 
       v2_admin.adminname, 
       v2_admin.login, 
       v2_admin.email, 
       v2_roles.id 
FROM   v2_admin 
       INNER JOIN v2_admin_roles 
               ON v2_admin.adminid = v2_admin_roles.adminid 
       INNER JOIN v2_roles 
               ON v2_admin_roles.roleid = v2_roles.id 
WHERE  v2_admin.adminid = 2

Provide sample data and what you want for a result. – OMG Ponies Feb 15 '13 at 15:13 — OMG Ponies, Feb 15 '13 at 15:13

sgeddes · Answer 1 · 2013-02-15T15:22:42.413

1

This is a little trick using FOR XML to convert the field to a list and STUFF to remove the beginning comma -- this should be close (untested):

SELECT v2_admin.adminid, 
       v2_admin.adminname, 
       v2_admin.login, 
       v2_admin.email, 
       STUFF(
        (
            SELECT ',' +  CAST(v2_roles.id as varchar) AS [text()]
                FROM v2_roles
                WHERE v2_admin_roles.roleid = v2_roles.id 
            ORDER BY v2_roles.id
            FOR XML PATH('')
        ), 1, 1, '') AS Roles
FROM   v2_admin 
       INNER JOIN v2_admin_roles 
               ON v2_admin.adminid = v2_admin_roles.adminid 
WHERE  v2_admin.adminid = 2 
GROUP BY v2_admin.adminid, 
       v2_admin.adminname, 
       v2_admin.login, 
       v2_admin.email

edited Feb 15 '13 at 15:22

answered Feb 15 '13 at 15:13

sgeddes

62,311
6
61
83

Thanks for that, but it give error - Conversion failed when converting the varchar value ',' to data type int. – nasaa Feb 15 '13 at 15:15
Cast your Roles.Id -- one sec and I'll edit... – sgeddes Feb 15 '13 at 15:16
@nasaa -- see edits -- you just needed to cast your int to a varchar -- best of luck! – sgeddes Feb 15 '13 at 15:19
Sorry but it still gives me two rows. Answer from KAF above has the solution to my problem. Thanks – nasaa Feb 15 '13 at 15:20
@nasaa -- no worries -- looks like I left off GROUP BY -- main key (to both of our answers), use FOR XML :) -- Glad we could help nonetheless... – sgeddes Feb 15 '13 at 15:22

score 1 · Accepted Answer · answered Feb 15 '13 at 15:17

This should also work,

;WITH mytable AS (
   SELECT v2_admin.adminid, 
       v2_admin.adminname, 
       v2_admin.login, 
       v2_admin.email, 
       v2_roles.id 
   FROM   v2_admin 
       INNER JOIN v2_admin_roles 
               ON v2_admin.adminid = v2_admin_roles.adminid 
       INNER JOIN v2_roles 
               ON v2_admin_roles.roleid = v2_roles.id 
   WHERE  v2_admin.adminid = 2 
)

SELECT t1.adminid,t1.adminname, t1.login, t1.email,
       roleIds =REPLACE( (SELECT convert(varchar,id) AS [data()]
                FROM mytable t2
                WHERE t2.adminid = t1.adminid
                --ORDER BY t2.adminid
                FOR XML PATH('')
            ), ' ', ' , ')
FROM mytable t1
GROUP BY t1.adminid,t1.adminname, t1.login, t1.email ;

Thanks. That works as expected – nasaa Feb 15 '13 at 15:17 — nasaa, Feb 15 '13 at 15:17

Grouping the results of multiple rows in one column

2 Answers2