在 T-sql 中使用 for XML 来透视数据 [英] Using for XML in T-sql to pivot data
本文介绍了在 T-sql 中使用 for XML 来透视数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用 for XML 函数来透视一些数据.我的数据如下:
I am attempting to use the for XML function to pivot some data. My data is as follows:
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
到目前为止我的代码:
; 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.
推荐答案
尝试
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
//结果
VenNum_A VenNum_B
0001 0002,0003,0004
0005 0006,0007,0008
希望能帮到你
这篇关于在 T-sql 中使用 for XML 来透视数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文