How we can loop over SQL Column with specific condition

Explanations:

1. In 1st picture data we have.
2. In the 2nd picture report we want to generate using SQL.
3. In the last we have added table and data script

4. Column Calculation = 2(Yes)/3(Total Column Contain Yes/NO) *100

Sample data:

    CREATE TABLE tableName 
(
    SNo INT,
    UserName    varchar(50),
    CL1 varchar(10),
    CL2 varchar(10),
    CL3 varchar(10),
    CL4 varchar(10),
    CL5 varchar(10),
    CL6 varchar(10),
    CL7 varchar(10),
    CL8 varchar(10),
    DateOfSubmit     VARCHAR(300)
);

INSERT INTO tableName (SNo,UserName,CL1,CL2,CL3,CL4,CL5,CL6,CL7,CL8,DateOfSubmit)
VALUES
    ('1', 'U1', 'YES', 'YES', 'YES', 'YES', 'YES', 'YES', 'YES', 'YES', 'Friday, January 1, 2021'),
    ('2', 'U1', 'YES', 'YES', 'YES', 'NO', 'YES', 'YES', 'YES', 'YES', 'Saturday, January 2, 2021'),
    ('3', 'U1', 'NO', 'YES', 'YES', 'NO', 'YES', 'YES', 'YES', 'YES', 'Sunday, January 3, 2021');

Answer

You need to unpivot the CL values first and then implement a dynamic pivot:

DECLARE @cols nvarchar(max)
DECLARE @stmt nvarchar(max)

SELECT @cols = STRING_AGG(QUOTENAME([YearMonth]), ',') WITHIN GROUP (ORDER BY [YearMonth])
FROM (
   SELECT CONVERT(
             varchar(6), 
             CONVERT(date, STUFF(DateOfSubmit, 1 , CHARINDEX(', ', DateOfSubmit), ''), 107), 
             112
          ) AS [YearMonth]
   FROM tableName
   UNION
   SELECT NULL
   WHERE 1 = 0
) t

SELECT @stmt = 
   N'SELECT UserName, C_Name,  ' + @cols +
   N'FROM ( ' +
      N'SELECT ' + 
         N't.UserName, ' +
         N'CONVERT(varchar(6), CONVERT(date, STUFF(t.DateOfSubmit, 1 , CHARINDEX('', '', t.DateOfSubmit), ''''), 107), 112) AS [YearMonth], ' +
         N'a.C_Name, ' +
         N'a.CL ' +
      N'FROM tableName t ' +
      N'CROSS APPLY (VALUES ' + 
         N'(''CL1'', CASE WHEN t.CL1 = ''YES'' THEN 100.0 ELSE 0.0 END), ' +
         N'(''CL2'', CASE WHEN t.CL2 = ''YES'' THEN 100.0 ELSE 0.0 END), ' +
         N'(''CL3'', CASE WHEN t.CL3 = ''YES'' THEN 100.0 ELSE 0.0 END), ' +
         N'(''CL4'', CASE WHEN t.CL4 = ''YES'' THEN 100.0 ELSE 0.0 END), ' +
         N'(''CL5'', CASE WHEN t.CL5 = ''YES'' THEN 100.0 ELSE 0.0 END), ' +
         N'(''CL6'', CASE WHEN t.CL6 = ''YES'' THEN 100.0 ELSE 0.0 END), ' +
         N'(''CL7'', CASE WHEN t.CL7 = ''YES'' THEN 100.0 ELSE 0.0 END), ' +
         N'(''CL8'', CASE WHEN t.CL8 = ''YES'' THEN 100.0 ELSE 0.0 END) ' +
      N') a (C_Name, CL) ' +
   N') x ' +
   N'PIVOT ( ' +
      N'AVG(CL)' +
      N'FOR [YearMonth] IN (' + @cols + ') ' +
   N') p'

EXEC sp_executesql @stmt

Results:

UserName C_Name 202101
--------------------------
U1       CL1    66.666666
U1       CL2    100.000000
U1       CL3    100.000000
U1       CL4    33.333333
U1       CL5    100.000000
U1       CL6    100.000000
U1       CL7    100.000000
U1       CL8    100.000000