SQL Server逗号分隔的列到多个列 [英] SQL Server comma-separated column to multiple columns

问题描述

我目前有一个三列的表格； Org ， Group ID 和 Role .

I currently have a table of three columns; Org, Group ID and Role.

名为 Org 的列可以忽略.

我一直想做的就是根据 Group ID 将它们分组，并拆分属于同一 Group ID的每个 Roles 到单列.

What I have been trying to do is to group them based on Group ID and split each of the Roles that belongs to the same Group ID to a single column.

到目前为止，我可以设法对记录进行分组，并将同一组的 Roles 放在同一列中.

So far I could manage to group the records and put the Roles of the same group in the same column.

我使用了以下脚本:

Select 
    [Group ID],  
    Substring((Select ',' +  [Role] 
               From [dbo].[ASA_test] B 
               Where B.[Group ID] = A.[Group ID]] 
               For XML Path('')), 2, 8000) As List  
From 
    [dbo].[ASA_test]  A
Group By 
    [Group ID] 

稍后，我创建了一个临时表变量，并在其中插入了上面的SQL，以便稍后在用户定义的函数中使用此变量以产生结果.

Later on I created a temp table variable and inserted the SQL above in it to use this variable in a user defined function afterwards to have the outcome.

请参见以下内容:

DECLARE @MyTempTableVariable TABLE
(
    GroupID int,
    Role nvarchar(255)
)

INSERT INTO @MyTempTableVariable
   Select 
       [Group ID],  
       Substring((Select ',' +  [Role] 
                  From [dbo].[ASA_test] B 
                  Where B.[Group ID] = A.[Group ID]] 
                  For XML Path('')), 2, 8000) As List  
   From 
       [dbo].[ASA_test]  A
   Group By 
       [Group ID] 

但是我无法继续前进，因为它没有按我预期的那样工作.

However I couldn't go any further as it didn't work as I expected.

有人可以帮忙吗?请注意，记录总数不受我在此处发布的内容的限制.

Can someone please help? please note that the total number of records are not limited with what I post here.

谢谢！

推荐答案

执行此操作.

SELECT  GroupID 
   ,STUFF((SELECT ', ' + CAST([Role] AS VARCHAR(10)) [text()]
     FROM [dbo].[ASA_test] 
     WHERE GroupID  = t.GroupID 
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM [dbo].[ASA_test]  t
GROUP BY GroupID

这篇关于SQL Server逗号分隔的列到多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！