1
Column1
--------
1,2,4
2,3,5
2,1,3

I have a column in a table which is comma-separated as shown above. From which I need to get below output:

No   Count
----------
1     2
2     3
3     2
4     1

When I tried with 

SELECT SUM(LEN(Holidays) - LEN(REPLACE(Holidays, ',', '')) + 1)  
FROM [dbo].[OhLog]

I'm not getting item wise count. Instead it is getting full count.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Limna
  • 401
  • 10
  • 28

3 Answers3

6

You can use CROSS_APPLY with STRING_SPLIT to create rows from the comma separated data values, and then COUNT the occurrences of each value:

SELECT value as [Holiday], COUNT(*) AS [Count]
FROM OhLog
CROSS APPLY STRING_SPLIT([Holidays], ',')
GROUP BY value

Output:

Holiday     Count
1           2
2           3
3           2
4           1
5           1

Demo on dbfiddle

If your database compatibility version is not at least 130, you won't have access to STRING_SPLIT. You can modify the compatibility version as described in the manual, or alternatively, use this query (based on this answer):

SELECT [Holiday], COUNT(*) AS [Count]
FROM (SELECT Split.a.value('.', 'NVARCHAR(MAX)') [Holiday]
      FROM (SELECT CAST('<X>'+REPLACE([Holidays], ',', '</X><X>')+'</X>' AS XML) AS String
            FROM Ohlog
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a)) AS O
GROUP BY [Holiday]

Output is the same as for the prior query. Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • It is showing this message 'Invalid object name 'STRING_SPLIT'' – Limna Dec 09 '19 at 07:07
  • 1
    @LimnaD'silva what is the compatibility level of your database? It needs to be at least 130 to use `STRING_SPLIT`. If it's below that, can you adjust it? See the [manual](https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?view=sql-server-ver15). If you're running SQL Server 2017, it should be 140... – Nick Dec 09 '19 at 07:10
  • @LimnaD'silva see my edit, I've added a solution which should work for lower compatibility version databases. – Nick Dec 09 '19 at 07:25
0

Please try:

SELECT 
    Num, count(*) Cnt from
(
    SELECT 
    LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Num
    FROM
    (
    SELECT CAST('<XMLRoot><RowData>' + REPLACE(Column1,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
    FROM   tbl
    )t
    CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
)x
GROUP BY Num

Demo Result

TechDo
  • 18,398
  • 3
  • 51
  • 64
0

So, you can use STRING_SPLIT function. It is supported by Sql Server 2017. more info here http://www.sqlservertutorial.net/sql-server-string-functions/sql-server-string_split-function/

It creates a table from string.

So, for each row in DB you will get a table. To Union it into one common table you have to use CROSS APPLY.

SELECT VALUE, COUNT(*) FROM test_for_parse CROSS APPLY STRING_SPLIT(array_value,',') GROUP BY VALUE

So, here is an example of code, that emulate your situation.

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8393f8dab667b528ed5aad7c61da74fe

TemaTre
  • 1,422
  • 2
  • 12
  • 20