T-SQL:拆分和聚合逗号分隔值 [英] T-SQL: split and aggregate comma-separated values
本文介绍了T-SQL:拆分和聚合逗号分隔值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下面的表格,每一行都有以逗号分隔的值:
ID
-------------------------------------------------- -----------------------
10031,10042
10064,10023,10060,10065,10003,10011,10009,10012, 10027,10004,10037,10039
10009
20011,10027,10032,10063,10023,10033,20060,10012,10020,10031,10011,20036,10041
我需要为每个 ID
>
我只是试图避免游标的实现,而且没有游标就如何做到这一点。
解决方案
您将要使用拆分函数:
create FUNCTION [dbo]。[Split](@ String varchar(MAX),@Delimiter char(1))
返回@temptable TABLE(items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
如果len(@String) < 1或@String为null return
while @idx!= 0
begin
set @idx = charindex(@ Delimiter,@ String)
if @ idx!= 0
set @slice = left(@ String,@ idx - 1)
else
set @slice = @String
if(len slice)> 0)
insert into @temptable(Items)values(@slice)
set @String = right(@ String,len(@String) - @idx)
if len(@String)= 0 break
end
return
end;
然后您可以按以下方式查询数据:
select item,count(items)
from table1 t1
cross apply dbo.split(t1.id,',')
按项目分类
I have the following table with each row having comma-separated values:
ID
-----------------------------------------------------------------------------
10031,10042
10064,10023,10060,10065,10003,10011,10009,10012,10027,10004,10037,10039
10009
20011,10027,10032,10063,10023,10033,20060,10012,10020,10031,10011,20036,10041
I need to get a count for each ID
(a groupby).
I am just trying to avoid cursor implementation and stumped on how to do this without cursors.
Any Help would be appreciated !
解决方案
You will want to use a split function:
create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
And then you can query the data in the following manner:
select items, count(items)
from table1 t1
cross apply dbo.split(t1.id, ',')
group by items
这篇关于T-SQL:拆分和聚合逗号分隔值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文