CODEWITHSUNDEEP
sql-server
A.T.
A.T.

Reputation: 26312

Concatenate column values against another column group

May be this is odd but i need to concatenate values of ActionId to corresponding group of roleId and Order by ActionID is must., some thing like 

ActionID  RoleId
"1357"     1
"2468"     2

Here is what i have currently, I am looking for GROUP_CONCAT equivalent in MS SQL. 

select av.ActionId, ra.RoleId from RoleAction ra join ActionValue av
on ra.ActionId = av.ActionId order by av.ActionId

ActionID    RoleId
1           1
3           1
5           1
7           1
4           2
2           2
6           2
8           2

Is there way to do that? Thanks in advance.

Upvotes: 0

Views: 44

Answers (2)

Abhishek Chaudhary
Abhishek Chaudhary

Reputation: 807

This should work:

    WITH CTE_A AS
    (
    select av.ActionId, ra.RoleId from RoleAction ra join ActionValue av
    on ra.ActionId = av.ActionId
    )
    SELECT DISTINCT A.RoleId,
    (SELECT '' + 
    CAST(B.ActionId AS varchar(10)) 
    FROM CTE_A B 
    WHERE B.RoleID = A.RoleID
    FOR XML PATH('')) AS ActionID
    FROM CTE_A A
    GROUP BY A.RoleID

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You can make it work using FOR XML PATH('') and an inner query:

SELECT DISTINCT T1.RoleID,
    (SELECT '' + ActionID 
     FROM RoleAction T2 
     WHERE T1.RoleID = T2.RoleID 
     ORDER BY ActionID 
     FOR XML PATH(''))
FROM RoleAction T1

Upvotes: 1

Related Questions