Merging results from SQL Query, where most values are the same

Question

Sorry for the not-so-great title.

I'm triyng to merge together sets of results to provide a quick view of some data.

If I have this query:

SELECT 
    tblPriv.ID, tblGroups.Name AS 'Group', tblPriv.User, tblPriv.Role 
FROM 
    tblPriv 
INNER JOIN 
    tblGroups on tblPriv.ID = tblGroups.ID

which returns these results:

ID    GROUP    USER    ROLE
---------------------------------
1     Taxes    DAVE    Admin
1     Taxes    JOHN    Admin
1     Taxes    BOB     PowerUser
2     Catering RON     Admin
2     Catering JACK    PowerUser
2     Catering JIM     PowerUser

(where ID is relational to say, a group, stored in another table)

What I ideally want to do is get 1 record for a group:

ID    GROUP        ADMINS          POWERUSERS
---------------------------------------------
1     Taxes        DAVE; JOHN;     BOB
2     Catering     RON;            JACK; JIM;

I know the roles beforehand - they always stay the same, and new roles are not added, ever.

How would I go about doing this? (I will also include data from other relational tables)

score 2 · Answer 1 · answered Jun 09 '16 at 12:20

SELECT 
DISTINCT tp.ID, 
tp.[GROUP], 
ISNULL(STUFF(Admins.Admins, 1, 1, ''), '') AS Admins,
ISNULL(STUFF(PowerUsers.PowerUsers, 1, 1, ''), '') AS PowerUsers 
FROM tlbPriv AS tp
OUTER APPLY
(
    SELECT ' ' + tmp.[USER] + ';' 
    FROM  tlbPriv AS tmp
    WHERE tmp.ID = tp.ID AND [tmp].[ROLE] = 'Admin' FOR XML PATH('')
)Admins(Admins)
OUTER APPLY
(
    SELECT ' ' + tmp.[USER] + ';' 
    FROM  tlbPriv AS tmp
    WHERE tmp.ID = tp.ID AND [tmp].[ROLE] = 'PowerUser' FOR XML PATH('')
)PowerUsers(PowerUsers)

score 1 · Accepted Answer · answered Jun 09 '16 at 11:31

For your particular problem:

select g.id, g.name,
       stuff((select '; ' + p.user
              from tblPriv p
              where p.id = g.id and p.role = 'Admin'
              for xml path ('') type
             ).value('', 'varchar(max)'
                    ), 1, 2, ''
            ) as admins,
       stuff((select '; ' + p.user
              from tblPriv p
              where p.id = g.id and p.role = 'PowerUser'
              for xml path ('') type
             ).value('', 'varchar(max)'
                    ), 1, 2, ''
            ) as PowerUsers
from tblGroups g

score 1 · Answer 3 · answered Jun 09 '16 at 12:05

Tagged tsql, I assume this is for MS SQL server:

DECLARE @users TABLE
    (
      [ID] INT ,
      [GROUP] VARCHAR(8) ,
      [USER] VARCHAR(4) ,
      [ROLE] VARCHAR(9)
    );

INSERT  INTO @users
        ( [ID], [GROUP], [USER], [ROLE] )
VALUES  ( 1, 'Taxes', 'DAVE', 'Admin' ),
        ( 1, 'Taxes', 'JOHN', 'Admin' ),
        ( 1, 'Taxes', 'BOB', 'PowerUser' ),
        ( 2, 'Catering', 'RON', 'Admin' ),
        ( 2, 'Catering', 'JACK', 'PowerUser' ),
        ( 2, 'Catering', 'JIM', 'PowerUser' );

WITH    data ( ID, [GROUP], [ROLE], [MEMBERS] )
          AS ( SELECT   u.ID ,
                        u.[GROUP] ,
                        u.ROLE ,
                        ( SELECT    [USER] + ';'
                          FROM      @users AS [u1]
                         WHERE     u.ID = u1.ID AND u.[GROUP] = u1.[GROUP] and u.ROLE = u1.ROLE
                        FOR
                          XML PATH('')
                        )
               FROM     @users AS [u]
             )
    SELECT  ID ,
            [GROUP] ,
            [Admin] AS [Admins] ,
            [PowerUser] AS [POWERUSERS]
    FROM    data PIVOT ( MAX(MEMBERS) FOR [ROLE] IN ( [Admin], [PowerUser] ) ) pvt;

score 0 · Answer 4 · answered Jun 09 '16 at 11:28

In MYSQL Use group concat

 SELECT 
   tblPriv.ID
   , tblGroups.Name AS 'Group'
   , group_concat(t1.User) AS ADMINS,  group_concat(t2.User)  AS POWERUSERS
FROM tblPriv 
INNER JOIN tblGroups as t1 on t1.ID = tblGroups.ID and t1.user='ADMIN'
INNER JOIN tblGroups as t2 on t2.ID = tblGroups.ID and t1.user='POWERUSER'
GROUP BY tblPriv.ID

Merging results from SQL Query, where most values are the same

4 Answers4