34,576
社区成员
发帖
与我相关
我的任务
分享
表结构如下:
id attID AttVal
1 1 a
2 1 b
3 1 c
现要如下结果 (str 是 AttVal 和id 组成字符串)
attid str
1 a|b|c,1|2|3
with tb(id,attID,AttVal)
as
(
select 1,1,'a' union all
select 2,1,'b' union all
select 3,1,'c' union all
select 4,2,'d' union all
select 5,2,'e'
)
select c.attID,
str=STUFF((select '|'+AttVal from tb b where b.attID=c.attID for XML path('')),1,1,'')+','+STUFF((select '|'+convert(nvarchar(20),id) from tb a where a.attID=c.attID for xml path('')),1,1,'') from tb c group by attID
結果
attID str
----------- --------------------
1 a|b|c,1|2|3
2 d|e,4|5
表结构如下:
id attID AttVal
1 1 a
2 1 b
3 1 c
4 2 d
5 2 e
现要如下结果 (str 是 AttVal 和id 组成字符串)
attid str
1 a|b|c,1|2|3
2 d|e,4|5