如何将T-SQL中的结果连接成列? [英] How to concatenate results in T-SQL into column?
本文介绍了如何将T-SQL中的结果连接成列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
| --Name-- | --Surname- - | - 语言 - | - 日期 - |
|詹姆斯Hetfield | en,gb,fr | 2011-01-01 |
|拉尔斯|乌尔里希gb,fr,ca | 2011-01-01 |
但我的选择获取一组行,如:
|詹姆斯Hetfield | en | 2011-01-01 |
|詹姆斯Hetfield | gb | 2011-01-01 |
|詹姆斯Hetfield | fr | 2011-01-01 |
|拉尔斯|乌尔里希gb | 2011-01-01 |
|拉尔斯|乌尔里希fr | 2011-01-01 |
|拉尔斯|乌尔里希ca | 2011-01-01 |
您推荐哪种最佳方法可以将一组结果转换成分组列中的逗号分隔值即时?我发现CROSS APPLY可以做这个工作,但是人们说这个方法很耗时。作为一个附加数据库有大量的数据。
提前感谢
问候,
Adrian
解决方案
这是最好的连接方法,它不会像其他XML方法一样扩展特殊字符:
- 与FOR XML&消除控制/编码的字符扩展&
set nocount on;
声明@YourTable表(RowID int,HeaderValue int,ChildValue varchar(5))
insert into @YourTable VALUES(1,1,'CCC')
insert into @YourTable VALUES(2 ,2,'B&B')
插入到@YourTable VALUES(3,2,'AAA')
insert into @YourTable VALUES(4,3,'< br> ')
insert into @YourTable VALUES(5,3,'A& Z')
set nocount off
SELECT
t1.HeaderValue
,STUFF(
(SELECT
','+ t2.ChildValue
FROM @YourTable t2
WHERE t1.HeaderValue = t2.HeaderValue
ORDER BY t2.ChildValue
FOR XML PATH(''),TYPE
).value('。','varchar(max)')
,1,2,''
)AS ChildValues
FROM @YourTable t1
GROUP BY t1.HeaderValue
OUTPUT:
HeaderValue ChildValues
----------- ---------------
1 CCC
2 AAA,B&B B
3< br>,A& Z
(3 row(s)affected)
I'm working on a query which should give me result like:
|--Name--|--Surname--|--Language--|--Date-- | | James | Hetfield | en,gb,fr | 2011-01-01| | Lars | Ulrich | gb,fr,ca | 2011-01-01|
but my select gets set of rows like:
| James | Hetfield | en | 2011-01-01| | James | Hetfield | gb | 2011-01-01| | James | Hetfield | fr | 2011-01-01| | Lars | Ulrich | gb | 2011-01-01| | Lars | Ulrich | fr | 2011-01-01| | Lars | Ulrich | ca | 2011-01-01|
Which best method you recommend to convert sets of result into coma-separated values in grouped column 'on the fly'? I've found that CROSS APPLY could do the work but people says that this method is very consuming. As an addition DB has huge amount of data.
Thanks in advance, Regards, Adrian
解决方案
here is the best concatenation method, it will not expand special characters like other XML methods:
--Concatenation with FOR XML & eliminating control/encoded char expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
t1.HeaderValue
,STUFF(
(SELECT
', ' + t2.ChildValue
FROM @YourTable t2
WHERE t1.HeaderValue=t2.HeaderValue
ORDER BY t2.ChildValue
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
,1,2, ''
) AS ChildValues
FROM @YourTable t1
GROUP BY t1.HeaderValue
OUTPUT:
HeaderValue ChildValues
----------- ---------------
1 CCC
2 AAA, B<&>B
3 <br>, A & Z
(3 row(s) affected)
这篇关于如何将T-SQL中的结果连接成列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文