Explanations:
- In 1st picture data we have.
- In the 2nd picture report we want to generate using SQL.
- In the last we have added table and data script
- 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');
CodePudding user response：
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
CodePudding user response：
This should be straight forward UNIONs and JOINs (for additional month-year). CLs columns seems to fix so UNION should be enough to achieve desired results.
select * from
(
select UserName, 'CL1' as C_Name
, convert(decimal(10, 2), sum(case CL1 when 'YES' then 1 else 0 end) * 1.0/count(1) * 100.00) as 'Jan-21'
from tableName
where year(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 2021
and month(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 1
group by UserName
union all
select UserName, 'CL2'
, convert(decimal(10, 2), sum(case CL2 when 'YES' then 1 else 0 end)/count(1) * 100.00)
from tableName
where year(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 2021
and month(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 1
group by UserName
union all
select UserName, 'CL3'
, convert(decimal(10, 2), sum(case CL3 when 'YES' then 1 else 0 end)/count(1) * 100.00)
from tableName
where year(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 2021
and month(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 1
group by UserName
union all
select UserName, 'CL4'
, convert(decimal(10, 2), sum(case CL4 when 'YES' then 1 else 0 end)/count(1) * 100.00)
from tableName
where year(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 2021
and month(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 1
group by UserName
union all
select UserName, 'CL5'
, convert(decimal(10, 2), sum(case CL5 when 'YES' then 1 else 0 end)/count(1) * 100.00)
from tableName
where year(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 2021
and month(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 1
group by UserName
union all
select UserName, 'CL6'
, convert(decimal(10, 2), sum(case CL6 when 'YES' then 1 else 0 end)/count(1) * 100.00)
from tableName
where year(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 2021
and month(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 1
group by UserName
union all
select UserName, 'CL7'
, convert(decimal(10, 2), sum(case CL7 when 'YES' then 1 else 0 end)/count(1) * 100.00)
from tableName
where year(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 2021
and month(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 1
group by UserName
union all
select UserName, 'CL8'
, convert(decimal(10, 2), sum(case CL8 when 'YES' then 1 else 0 end)/count(1) * 100.00)
from tableName
where year(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 2021
and month(cast(right(DateOfSubmit, charindex(',', reverse(DateOfSubmit)) - 1) as date)) = 1
group by UserName
) as t
CodePudding user response：
That is a simple UNPIVOT query.
select
username,
c_name,
avg(case when yes_or_no = 'YES' then 1.0 else 0.0 end) as ratio
from tablename
unpivot (yes_or_no FOR c_name IN (CL1,CL2,CL3,CL4,CL5,CL6,CL7,CL8)) unpvt
group by username, c_name
order by username, c_name;
Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5a3fb6ebb652ca34beca1792e98cf900