在T-sql中使用for XML来转移数据

I am attempting to use the for XML function to pivot some data. My data is as follows:

我试图使用for XML函数来转移一些数据。我的数据如下:

VenNum_A   VenNum_B
0001       0002
0001       0003
0001       0004
0005       0006
0005       0007
0005       0008

I am attempting to get the following result:

我试图得到以下结果:

venNum_A   VenNum_B
0001       0002,0003,0004
0005       0006,0007,0008

My Code so far:

我的代码到目前为止:

; with t as
        (
        select Distinct 
            A_VenNum, B_VenNum, SUM(1) as Cnt
        From 
            #VenDups_Addr
        Group by 
            A_VenNum, B_VenNum
        )
        select distinct
            B_Vennum,
             A_Vennum =cast(substring((
            select distinct 
                  [text()] =  ', ' + t1.A_Vennum 
            from 
                  t as t1
            where 
                t.A_Vennum =t1.A_VenNum
            for XML path('')
                ),3,99) as Varchar(254))

        From t

Currently my results are no different than selecting both original fields.

目前,我的结果与选择两个原始字段没有什么不同。

Also if this is not the best method of reaching my end goal I am totally open to an alternate solution, This is the only way I know of doing this.

此外,如果这不是达到我最终目标的最佳方法,我完全接受另一种解决方案,这是我知道这样做的唯一方法。

2 个解决方案

#1

Try

Declare @t table(VenNum_A VARCHAR(10),   VenNum_B VARCHAR(10))
Insert Into @t 
Select '0001','0002' Union All Select '0001','0003' Union All Select '0001','0004' Union All 
Select '0005','0006' Union All Select '0005','0007' Union All Select '0005','0008'

SELECT 
    VenNum_A
    ,VenNum_B = STUFF((Select ',' + CAST(VenNum_B AS VARCHAR(1000)) 
      FROM  @t t2 
      WHERE t1.VenNum_A = t2.VenNum_A
      FOR XML PATh ('')
      ),1,1,'')
FROM @t t1
GROUP BY t1.VenNum_A

//Result

VenNum_A    VenNum_B
0001    0002,0003,0004
0005    0006,0007,0008

Hope this helps

希望这可以帮助

#2

Are you aware that CodePlex has an open-source CLR implementation of user defined aggregate GROUP_CONCAT .Installation is as simple as running a SQL script on your server.

您是否知道CodePlex具有用户定义聚合GROUP_CONCAT的开源CLR实现。安装就像在服务器上运行SQL脚本一样简单。

http://groupconcat.codeplex.com/

it has 4 group_concat implementation

它有4个group_concat实现

GROUP_CONCAT --default delimiter is , (comma)

GROUP_CONCAT - 默认分隔符是,(逗号)
GROUP_CONCAT_D -- you can specify the delimiter

GROUP_CONCAT_D - 您可以指定分隔符
GROUP_CONCAT_DS -- you can specify the delimiter ,sort order (1 as asc order ,2 as desc order)

GROUP_CONCAT_DS - 您可以指定分隔符,排序顺序(1作为asc顺序,2作为desc顺序)
GROUP_CONCAT_S -- you can specify sort order

GROUP_CONCAT_S - 您可以指定排序顺序

In your example you will use it like this

在您的示例中,您将使用它

SELECT  VenNum_A        
       ,dbo.GROUP_CONCAT(VenNum_B) AS VenNum_B        
FROM YourTable
GROUP BY
    VenNum_A

#1

Try

Declare @t table(VenNum_A VARCHAR(10),   VenNum_B VARCHAR(10))
Insert Into @t 
Select '0001','0002' Union All Select '0001','0003' Union All Select '0001','0004' Union All 
Select '0005','0006' Union All Select '0005','0007' Union All Select '0005','0008'

SELECT 
    VenNum_A
    ,VenNum_B = STUFF((Select ',' + CAST(VenNum_B AS VARCHAR(1000)) 
      FROM  @t t2 
      WHERE t1.VenNum_A = t2.VenNum_A
      FOR XML PATh ('')
      ),1,1,'')
FROM @t t1
GROUP BY t1.VenNum_A