說明：
- 在第一張圖片資料中，我們有。
- 在第二張圖片報告中，我們要使用 SQL 生成。
- 最后我們添加了表格和資料腳本
- 列計算 = 2（是）/3（總列包含是/否）*100
樣本資料：
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');
uj5u.com熱心網友回復：
您需要先取消CL透視值，然后實作動態透視：
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
結果：
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
uj5u.com熱心網友回復：
這應該是直截了當UNIONs的JOINs（對于額外的月份）。CLs 列似乎已修復，因此UNION應該足以達到預期的結果。
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
uj5u.com熱心網友回復：
這是一個簡單的UNPIVOT查詢。
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;
演示：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5a3fb6ebb652ca34beca1792e98cf900
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/410538.html
標籤：
上一篇：用于物體框架的SQLServer