none
Group by Concatenation of rows in sql server

    Question

  • Hi

    I have tables like below

    USER TABLE

    _________________________________________________

    ID   | Name   | Date

    -----------------------------------------------------------

    1 | A |

    2 | A |

    3 | A |

    4 | B |

    5 | C |

    6 | C |

    ------------------------------------------------------------

    AND ROLE TABLE

    --------------------------------------------------

    ID | ROLE

    --------------------------------------------------

    1 | ADMIN

    2 | PRJ MGR

    3 | LEAD

    4 | ADMIN

    5 PRJ MGR

    6 | LEAD

    and i want result like below

    ___________________________

    NAME  | ROLE

    ---------------------------------------------

    A | ADMIN , PRJ MGR , LEAD

    B | ADMIN

    C | PRJ MGR, LEAD

    i have used STUFF method but it concatenate the string if the id or unique value only in the same table not with other table

    Thanks

    Wednesday, July 24, 2013 10:02 PM

Answers

  • Try -

    DECLARE @User TABLE(Id INT ,Name VARCHAR(15))
    INSERT INTO @User
    VALUES(1,'A')
    ,(2,'A')
    ,(3,'A')
    ,(4,'B')
    ,(5,'C')
    ,(6,'C')
    SELECT *
    FROM @User
             
    DECLARE @Role TABLE(Id INT ,[Roles] VARCHAR(15))
    INSERT INTO @Role
    VALUES(1,'ADMIN')
    ,(2,'PRJ MGR')
    ,(3,'LEAD')
    ,(4,'ADMIN')
    ,(5,'PRJ MGR')
    ,(6,'LEAD')
    SELECT *
    FROM @Role
    SELECT u.Name
    	,r.Roles
    INTO #tmp
    FROM @Role AS r
    	INNER JOIN @User AS u
    		ON r.Id = u.Id
    ORDER BY r.Id
    SELECT DISTINCT Name
    	,STUFF((SELECT DISTINCT ',' + b.Roles
    		 FROM #tmp AS b
    		 WHERE a.NAME = b.Name
    		 FOR XML PATH('')),1,1,'') AS [ConcatRoles]
    FROM #tmp AS a
    DROP TABLE #tmp

    This might not be a good solution...but give a try. Used #tmp to keep the order of the Roles in User table. If you don't care about the order of the concatenation of Roles, then you use a Subquery / Cte and then concatenate.

    Narsimha

    Wednesday, July 24, 2013 10:55 PM

All replies

  • Try -

    DECLARE @User TABLE(Id INT ,Name VARCHAR(15))
    INSERT INTO @User
    VALUES(1,'A')
    ,(2,'A')
    ,(3,'A')
    ,(4,'B')
    ,(5,'C')
    ,(6,'C')
    SELECT *
    FROM @User
             
    DECLARE @Role TABLE(Id INT ,[Roles] VARCHAR(15))
    INSERT INTO @Role
    VALUES(1,'ADMIN')
    ,(2,'PRJ MGR')
    ,(3,'LEAD')
    ,(4,'ADMIN')
    ,(5,'PRJ MGR')
    ,(6,'LEAD')
    SELECT *
    FROM @Role
    SELECT u.Name
    	,r.Roles
    INTO #tmp
    FROM @Role AS r
    	INNER JOIN @User AS u
    		ON r.Id = u.Id
    ORDER BY r.Id
    SELECT DISTINCT Name
    	,STUFF((SELECT DISTINCT ',' + b.Roles
    		 FROM #tmp AS b
    		 WHERE a.NAME = b.Name
    		 FOR XML PATH('')),1,1,'') AS [ConcatRoles]
    FROM #tmp AS a
    DROP TABLE #tmp

    This might not be a good solution...but give a try. Used #tmp to keep the order of the Roles in User table. If you don't care about the order of the concatenation of Roles, then you use a Subquery / Cte and then concatenate.

    Narsimha

    Wednesday, July 24, 2013 10:55 PM
  • Hi,

    The User and Role have a MTM relationship here. This should be stored in a separate table.

    You should store UserXRole mapping like below for example:

    USER TABLE
    UserID UserName
    1 A
    2 B
    3 C

    ROLE TABLE
    RoleID RoleName
    1 ADMIN
    2 PRJ MGR
    3 LEAD
    4 PRJ MGR
    5 LEAD

    USER_ROLES TABLE
    UserID RoleID
    1 1
    1 2
    1 3
    2 3
    2 5
    3 5



    - Vishal

    SqlAndMe.com

    Thursday, July 25, 2013 4:36 AM