如何查找SQL Server中特定列中逗号分隔值的重复次数?

I have a SQL Server table as shown below, in that one column contains comma-separated integer values. What I want is to get particular number with count as below expected result

我有一个SQL Server表，如下所示，其中一列包含逗号分隔的整型值。我想要的是得到如下预期结果的特定数字

Edited: I know how to split the comma separated value. but my problem is output that I want. see the expected output in that count column shows in all table howmnay times particular number repeat.

编辑:我知道如何分割逗号分隔的值。但我的问题是我想要的输出。在所有表howmnay中，看到预期的输出显示在所有表中。

For example :

例如:

Id     values
---------------
1      2,3
2      1,2,3
3      1,3
4      2,3

Expected result :

预期结果:

number  repeat count
--------------
1       2
2       3
3       4

Can anyone help me with this? How to write the query to get this desired output?

有人能帮我一下吗?如何编写查询以获得所需的输出?

Thanks in advance

谢谢提前

2 个解决方案

#1

It looks like the question is how to aggregate the results of a SPLIT function, not how to split the values.

看起来问题是如何聚合一个分割函数的结果，而不是如何分割值。

SQL Server 2016 provides the built-in STRING_SPLIT function to split a delimited string and return the values as a table. Individual values are returned in the value field. The following query groups the value field and returns the count:

SQL Server 2016提供了内置的STRING_SPLIT函数来分割分隔带分隔符的字符串并将值作为表返回。在值字段中返回单个值。下面的查询将值字段分组并返回计数:

declare @table table (id int, somevalues nvarchar(200))

insert into @table
values
(1,N'2,3'),
(2,N'1,2,3'),
(3,N'1,3'),
(4,N'2,3')

select value,count(* )
from @table 
    cross apply string_split(somevalues,',')
group by value

The same query can be used in previous versions as long as a split function is available. Almost all of the available techniques are described in Aaron Bertrand's articles like this one and this follow up. The fastest methods use CLR and XML.

只要分割函数可用，在以前的版本中也可以使用相同的查询。几乎所有可用的技术都是在Aaron Bertrand的文章中描述的，比如这个和后续。最快的方法使用CLR和XML。

The queries are the same, the only things that change are the names of the columns returned by the split function, eg:

查询是相同的，唯一改变的是分割函数返回的列的名称，例如:

select item,count(* )
from @table 
    cross apply dbo.SplitStrings_XML(somevalues,',')
group by item

In both cases the result is :

在这两种情况下，结果是:

value   (No column name)
1       2
2       3
3       4

#2

First create split function like this

首先创建这样的分割函数

    CREATE FUNCTION SplitString (
        @Input NVARCHAR(MAX)
        , @Character CHAR(1)
        )
    RETURNS @Output TABLE (Item NVARCHAR(1000))
    AS
    BEGIN
        DECLARE @StartIndex INT
            , @EndIndex INT

        SET @StartIndex = 1

        IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
        BEGIN
            SET @Input = @Input + @Character
        END

        WHILE CHARINDEX(@Character, @Input) > 0
        BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output (Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
        END

        RETURN
    END
    GO

then you can adapt the query as follows

然后您可以将查询调整为如下所示。

    create table #temp(
    id int,
    test varchar(20)
    )
    insert into #temp (id,test) values (1,'1,2,3')


     SELECT t.id, count(sf.Item)
    FROM #temp AS t
    CROSS APPLY dbo.SplitString(t.test,',') AS sf
    group by t.id;

    drop table #temp

#1