我正在使用通用系统进行报告,从数据库视图(sql Server 2005)获取数据.在这个视图中,我不得不在一行中从一对多关系中合并数据,并使用
priyanka.sarkar在此线程中描述的解决方案：
Combine multiple results in a subquery into a single comma-separated value.该解决方案使用sqlXML来合并数据(子查询)：
SELECT STUFF(
( SELECT ',' + Name
FROM MyTable _in
WHERE _in.ID = _out.ID
FOR XML PATH('')),-- Output multiple rows as one xml type value,-- without xml tags
1,2,'') -- STUFF: Replace the comma at the beginning with empty string
FROM MyTable _out
GROUP BY ID -- Removes duplicates
这完美的工作(甚至没有那么沉重的表现),除了我的数据现在通过sqlXML获得XML编码(& =& amp; amp; amp; amp;等) – 我不想要XML数据,我只是把它用作一个诡计 – 由于通用系统,我无法对其进行编码以将其清理干净,因此编码数据直接转到报告中.我不能使用通用系统的存储过程,所以CURSOR合并或COALESCE-ing在这里不是一个选项…
所以我正在寻找的是T-sql中的一种方式,可以让我再次解码XML,甚至更好：避免sqlXML进行编码.显然,我可以编写一个这样做的存储函数,但是我更喜欢内置,更安全的方式…
谢谢你的帮助…
如果将类型指定为xml的选项,则可以使用XPath查询将XML类型转换回varchar.使用示例表变量：
原文链接：https://www.f2er.com/xml/292940.htmldeclare @MyTable table (id int,name varchar(50)) insert @MyTable (id,name) select 1,'Joel & Jeff' union all select 1,'<<BIN LADEN>>' union all select 2,'&&BUSH&&'
一个可能的解决方案是：
select b.txt.query('root').value('.','varchar(max)')
from (
select distinct id
from @MyTable
) a
cross apply
(
select CASE ROW_NUMBER() OVER(ORDER BY id) WHEN 1 THEN ''
ELSE ',' END + name
from @MyTable
where id = a.id
order by
id
for xml path(''),root('root'),type
) b(txt)
这将打印：
Joel & Jeff,<<BIN LADEN>> &&BUSH&&
这是一个没有XML转换的替代方案.它确实有一个递归查询,所以性能里程可能会有所不同.是从Quassnoi’s blog：
;WITH with_stats(id,name,rn,cnt) AS
(
SELECT id,ROW_NUMBER() OVER (PARTITION BY id ORDER BY name),COUNT(*) OVER (PARTITION BY id)
FROM @MyTable
),with_concat (id,gc,CAST(name AS VARCHAR(MAX)),cnt
FROM with_stats
WHERE rn = 1
UNION ALL
SELECT with_stats.id,with_stats.name,CAST(with_concat.gc + ',' + with_stats.name AS VARCHAR(MAX)),with_stats.rn,with_stats.cnt
FROM with_concat
JOIN with_stats
ON with_stats.id = with_concat.id
AND with_stats.rn = with_concat.rn + 1
)
SELECT id,gc
FROM with_concat
WHERE rn = cnt
OPTION (MAXRECURSION 0)
