具有不同字符的 SQL Server 组连接 [英] SQL Server Group Concat with Different characters
问题描述
我查看了许多在 SQL Server 中模拟Group concat"功能的解决方案.不过,我想制定一个更易读的解决方案,但我不知道该怎么做.
I have looked through a number of solutions to emulating "Group concat" functionality in SQL Server. I wanted to make a more human readable solution though and I can't work out how to do it.
我有一个观点:
ParentID | ChildName
其中包含记录,例如:
1 | Max
1 | Jessie
2 | Steven
2 | Lucy
2 | Jake
3 | Mark
我想对这些进行分组连接"以获得:
I want to "Group Concat" these to get:
1 | Max and Jessie
2 | Steven, Lucy and Jake
3 | Mark
因此,如果只有 1 个孩子,只需返回姓名,如果有多个,则将最后 2 个与 ' 和 ' 连接起来,其他所有用 ', ' 连接.
So If there is only 1 child, just return name, if there are more than one, concat the last 2 with an ' and ' and all others with a ', '.
我对如何在不求助于 CLR 的情况下做到这一点感到有些困惑,我不想这样做.我对一个功能很满意 - 但速度是个问题,我如何确定子编号以便我可以在和"、、"或"之间进行选择?
I am a bit stuck on how to do this without resorting to CLR, which I don't want to do. I am happy with a function - but speed is an issue and how do I determine the child number so I can choose between ' and ', ', ' or ''?
推荐答案
制作更易读的解决方案
make a more human readable solution
抱歉,这是我对您的要求所能做的最好的事情.
Sorry, this is the best I can do with your requirement.
MS SQL Server 2008 架构设置:
create table YourTable
(
ParentID int,
ChildName varchar(10)
);
insert into YourTable values
(1, 'Max'),
(1, 'Jessie'),
(2, 'Steven'),
(2, 'Lucy'),
(2, 'Jake'),
(3, 'Mark');
查询 1:
with T as
(
select ParentID,
ChildName,
row_number() over(partition by ParentID order by ChildName) as rn,
count(*) over(partition by ParentID) as cc
from YourTable
)
select T1.ParentID,
(
select case
when T2.rn = 1 and T2.cc > 1 then ' and '
else ', '
end + T2.ChildName
from T as T2
where T1.ParentID = T2.ParentID
order by T2.rn desc
for xml path(''), type
).value('substring(text()[1], 3)', 'varchar(max)') as ChildNames
from T as T1
group by T1.ParentID
结果:
| PARENTID | CHILDNAMES |
------------------------------------
| 1 | Max and Jessie |
| 2 | Steven, Lucy and Jake |
| 3 | Mark |
这篇关于具有不同字符的 SQL Server 组连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!