How we can loop over SQL Column with specific condition

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