SQL 一对多下的行转列

SQL查询一对多时，通常查询多列再处理:

select RoleCode,MenuID from A_RoleMenuShips where RoleCode=1308 

但有些情况下希望直接返回如下结果：

 

如何实现？

1.Oracle可以使用wm_concat()

select RoleCode,WM_CONCAT(MenuID) AS MenuIDs from A_RoleMenuShips where RoleCode=1308 GROUP BY RoleCode;

 

2.MySQL可以使用group_concat

select RoleCode,GROUP_CONCAT(MenuID) AS MenuIDs from A_RoleMenuShips where RoleCode=1308 GROUP BY RoleCode;

 

3.SqlServer使用stuf

• SELECT
         RoleCode,
  　　 [MenuID] = stuff((
              SELECT ',' + [MenuID] 
              FROM A_RoleMenuShips t 
              WHERE t.id = A_RoleMenuShips.id 
              FOR xml path('')) , 1 , 1 , '')  
  FROM A_RoleMenuShips 
  GROUP BY RoleCode;           
  

   

  最后，可以加上join

   

  select A_RoleMenuShips.RoleCode,GROUP_CONCAT(A_Menus.Title) from A_RoleMenuShips LEFT JOIN A_Menus on A_RoleMenuShips.MenuID=A_Menus.MenuID where A_RoleMenuShips.RoleCode=1308