SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE OhLog
    ([Holidays] varchar(20))
INSERT INTO OhLog
    ([Holidays])
VALUES
    ('1,2,4'),
('2,3,5'),
('2,1,3')
3 rows affected

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


Holiday
Count


1
2

2
3

3
2

4
1

5
1


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]


Holiday
Count


1
2

2
3

3
2

4
1

5
1