How to find how many times repeat number in comma separated value in particular column in SQL Server

Question

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

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.

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

Use STRING_SPLIT in SQL Server 2016+ to get the individual values. Or *don't* store such strings in the first place. Use a separate table — Panagiotis Kanavos, Feb 08 '18 at 12:19
i am using sql server 2012 database and all working fine with this comma separated value but only in one place i stucked to create a report where i want output like this — tgeek001, Feb 08 '18 at 12:22
There are literally 1,000's of answers on how to split a delimited string in SQL Server. One such example is [delimitedSplit8K](http://www.sqlservercentral.com/articles/Tally+Table/72993/). There's a lots of example on this website as well on how to do so. — Thom A, Feb 08 '18 at 12:30
Possible duplicate of [Split function equivalent in T-SQL?](https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) (note I don't recommend the accepted solution on this link, as it uses a LOOP, there are better answers later on). — Thom A, Feb 08 '18 at 12:30
Which is why it's not fine. If you want to *query* the data, it has to be in a separate table. This breaks even the 1st normal form. Anyway, there are a LOT of duplicate questions that ask how to split a string. All of them in the end describe on of the methods [in this article](https://sqlperformance.com/2012/07/t-sql-queries/split-strings). The XML technique is the fastest after CLR — Panagiotis Kanavos, Feb 08 '18 at 12:32
And Aaron Bertrand's [follow up article](https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up) that compares the improved delimitedSplit8 to the other techniques — Panagiotis Kanavos, Feb 08 '18 at 12:34
@PanagiotisKanavos for your inputs. actually I know about split function in sql and i am using it to comma separated value in table form. but the thing is i want which number used how many time in that comma separate column which is little complex — tgeek001, Feb 08 '18 at 12:39
@tgeek001 that's not what you asked in the question. The question asks how to split, not how to perform aggregations on STRING_SPLIT's results. In any version, as long as the function returns a table you can use it with CROSS APPLY and aggregate the results. — Panagiotis Kanavos, Feb 08 '18 at 12:46

score 2 · Accepted Answer · answered Feb 08 '18 at 12:55

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:

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.

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

thanks @Panagiotis Kanavos your solution worked for me. I was trying to similar thing but was doing inner join instead of cross join which was creating issue for me, anyway i am accepting your solution. And I need to change my split function as i am using the slower one which you mentioned. — tgeek001, Feb 08 '18 at 13:12

score 0 · Answer 2 · answered Feb 08 '18 at 12:58

0

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

answered Feb 08 '18 at 12:58

Seyfullah ÖZDEMİR

21
4

2

There are hundreds of questions that show how to create a split function. This one is the slowest of them. – Panagiotis Kanavos Feb 08 '18 at 13:02
Did you compare all of them ? – Seyfullah ÖZDEMİR Feb 08 '18 at 14:00
As I said, there are hundreds of similar questions. It's not a *new* problem. Looping was used before 2005. XML and CLR came later. Aaron Bertrand's articles are the source for most the SO answers either directly or indirectly. Looping or using cursors in SQL is the slowest technique in any case – Panagiotis Kanavos Feb 08 '18 at 14:10
You won't find the loop in the articles because it doesn't scale at all. Aaron Bertrand investigated it as well [back in 2010](https://sqlblog.org/2010/07/07/splitting-a-list-of-integers-another-roundup). It's actually the worst – Panagiotis Kanavos Feb 08 '18 at 14:13

How to find how many times repeat number in comma separated value in particular column in SQL Server

2 Answers2