T-SQL: split and aggregate comma-separated values

问题:

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 !

回答1:

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

See SQL Fiddle With Demo

回答2:

Well, the solution i always use, and probably there might be a better way, is to use a function that will split everything. No use for cursors, just a while loop.

if OBJECT_ID('splitValueByDelimiter') is not null
begin
  drop function splitValueByDelimiter
end
go
create function splitValueByDelimiter (
  @inputValue varchar(max)
, @delimiter varchar(1)
)       
returns @results table (value varchar(max))       
as       
begin       

declare @delimeterIndex int       
      , @tempValue varchar(max)

set @delimeterIndex = 1
while @delimeterIndex > 0  and len(isnull(@inputValue, '')) > 0    
begin       
  set @delimeterIndex = charindex(@delimiter, @inputValue)       
  if @delimeterIndex > 0       
    set @tempValue = left(@inputValue, @delimeterIndex - 1)       
  else       
      set @tempValue = @inputValue       

  if(len(@tempValue)>0)  
  begin
    insert 
    into    @results 
    select  @tempValue
  end

  set @inputValue = right(@inputValue, len(@inputValue) - @delimeterIndex)       

end   

return       
end  

After that you can call the output like this :

if object_id('test') is not null
begin
  drop table test
end
go
create table test (
  Id varchar(max)
)

insert 
into test
          select '10031,10042'
union all select     '10064,10023,10060,10065,10003,10011,10009,10012,10027,10004,10037,10039'
union all select '10009'
union all select     '20011,10027,10032,10063,10023,10033,20060,10012,10020,10031,10011,20036,10041'

select  value
from    test
        cross apply splitValueByDelimiter(Id, ',')

Hope it helps, although i am still looping through everything

回答3:

After reiterating the comment above about NOT putting multiple values into a single column (Use a separate child table with one value per row!),

Nevertheless, one possible approach: use a UDF to convert delimited string to a table. Once all the values have been converted to tables, combine all the tables into one table and do a group By on that table.

Create Function dbo.ParseTextString (@S Text, @delim VarChar(5))
Returns @tOut Table 
(ValNum Integer Identity Primary Key, 
 sVal VarChar(8000))
As
Begin 
Declare @dlLen TinyInt      -- Length of delimiter
Declare @wind VarChar(8000) -- Will Contain Window into text string
Declare @winLen  Integer    -- Length of Window
Declare @isLastWin TinyInt  -- Boolean to indicate processing Last Window
Declare @wPos  Integer      -- Start Position of Window within Text String
Declare @roVal VarChar(8000)-- String Data to insert into output Table
Declare @BtchSiz Integer    -- Maximum Size of Window
Set @BtchSiz = 7900     -- (Reset to smaller values to test routine)
Declare @dlPos Integer      -- Position within Window of next Delimiter
Declare @Strt Integer       -- Start Position of each data value within Window
-- -------------------------------------------------------------------------

-- ---------------------------
If @delim is Null Set @delim = '|'
If DataLength(@S) = 0 Or
    Substring(@S, 1, @BtchSiz) = @delim Return
-- --------------------------------------------
Select @dlLen = DataLength(@delim),
        @Strt = 1, @wPos = 1,
        @wind = Substring(@S, 1, @BtchSiz)
Select @winLen = DataLength(@wind),
    @isLastWin = Case When DataLength(@wind) = @BtchSiz
                      Then 0 Else 1 End,
        @dlPos = CharIndex(@delim, @wind, @Strt)
-- --------------------------------------------
While @Strt <= @winLen
    Begin
        If @dlPos = 0 Begin    -- No More delimiters in window
            If @isLastWin = 1 Set @dlPos = @winLen + 1 
            Else Begin
                Set @wPos = @wPos + @Strt - 1
                Set @wind = Substring(@S, @wPos, @BtchSiz)
                -- ----------------------------------------
                Select @winLen = DataLength(@wind), @Strt = 1,
                       @isLastWin = Case When DataLength(@wind) = @BtchSiz
                                Then 0 Else 1 End,
                       @dlPos = CharIndex(@delim, @wind, 1)
                If @dlPos = 0 Set @dlPos = @winLen + 1 
            End
        End
        -- -------------------------------
        Insert @tOut (sVal) 
        Select LTrim(Substring(@wind, 
                @Strt, @dlPos - @Strt))
        -- -------------------------------
        -- Move @Strt to char after last delimiter
        Set @Strt = @dlPos + @dlLen 
        Set @dlPos = CharIndex(@delim, @wind, @Strt)
    End
Return
End

Then write, (using your table schema),

 Declare @AllVals VarChar(8000)
 Select @AllVals = Coalesce(@allVals + ',', '') + ID 
 From Table Where ID Is Not null
 -- -----------------------------------------
 Select sVal, Count(*)
 From dbo.ParseTextString(@AllVals, ',')
 Group By sval