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