Count many columns have the same value

Question

How do I write an SQL query to return count the similar values for each column in one row?

I have this:

emp_no	d1	d2	d3	d4	d5	d6	d7	d8	d9	d10	date
1002	2	2	2	26	26	4	4	53	53	53	2021-03-31
1003	4	4	4	26	26	2	26	26	26	26	2021-03-31
1002	2	2	2	26	26	4	4	26	26	26	2021-04-30

I want the result like this:

emp_no	2	4	26	51	53	date
1002	3	2	2	0	3	2021-03-31
1003	1	3	6	0	0	2021-03-31
1002	3	2	2	0	3	2021-04-30

I try UNPIVOT data, but how I can pivot this?

Do I create a view with unpivot data and after that re-pivot the aggregated data?

SELECT EMP_NO, TS_MTH_YR, TSS_D
FROM (
    SELECT EMP_NO, TS_MTH_YR, [D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10]
    FROM TSS_MONTHLY_TS
) AS TSS
UNPIVOT (
    TSS_D FOR TSS_DAYS IN ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10])
) AS TS

Answer 1

As I mentioned in the comments, you'll need to both unpivot and then repivot your data here. One method would therefore be the below:

WITH YourTable AS(
    SELECT emp_no ,d1 ,d2 ,d3 ,d4 ,d5 ,d6 ,d7 ,d8 ,d9 ,d10 , CONVERT(date,date) AS date --That's not confusing
    FROM (VALUES(1002,2,2,2,26,26,4,4 ,53 ,53 ,53 ,'2021-03-31'),
                (1003,4,4,4,26,26,2,26, 26, 26, 26,'    2021-03-31'),
                (1002,2,2,2,26,26,4,4 ,26 ,26 ,26 ,'2021-04-30'))V(emp_no ,d1 ,d2 ,d3 ,d4 ,d5 ,d6 ,d7 ,d8 ,d9 ,d10 ,date))
SELECT YT.emp_no,
       COUNT(CASE V.Val WHEN 2 THEN 1 END) AS [2],
       COUNT(CASE V.Val WHEN 4 THEN 1 END) AS [4],
       COUNT(CASE V.Val WHEN 26 THEN 1 END) AS [26],
       COUNT(CASE V.Val WHEN 51 THEN 1 END) AS [51],
       COUNT(CASE V.Val WHEN 53 THEN 1 END) AS [53],
       YT.[date]
FROM YourTable YT
     CROSS APPLY (VALUES('d1',YT.d1),
                        ('d2',YT.d2),
                        ('d3',YT.d3),
                        ('d4',YT.d4),
                        ('d5',YT.d5),
                        ('d6',YT.d6),
                        ('d7',YT.d7),
                        ('d8',YT.d8),
                        ('d9',YT.d9),
                        ('d10',YT.d10))V(Col,Val)
GROUP BY YT.emp_no,
         YT.[date];