我需要帮助编写矩阵样式报告的查询。
我的数据格式如下
id body_part incident_type1 incident_type2 incident_type3
1 head PPE null null
2 ankle Unsafe Act Facility null
3 hand null null null
4 head Facility PPE Unsafe Act
我希望行是主体部分,列是事件类型。如果incident_type1为null,那么我想要一个"n/a"列中的计数。但是,如果incident_type2和/或3为null,我不希望这些计数在"n/a"列中。
Facility Unsafe Act PPE N/A
ankle 1 1 0 0
hand 0 0 0 1
head 1 1 2 0
有一种方法:
select body_part
, Facility = sum(case when incident_type1 = 'Facility' or incident_type2 = 'Facility' or incident_type3 = 'Facility' then 1 else 0 end)
, [Unsafe Act] = sum(case when incident_type1 = 'Unsafe Act' or incident_type2 = 'Unsafe Act' or incident_type3 = 'Unsafe Act' then 1 else 0 end)
, PPE = sum(case when incident_type1 = 'PPE' or incident_type2 = 'PPE' or incident_type3 = 'PPE' then 1 else 0 end)
, [N/A] = sum(case when incident_type1 is null then 1 else 0 end)
from Incidents
group by body_part
order by body_part
SQL Fiddle with demo.
这里假设事件类型已知,并且同一行不会多次具有相同的事件类型。
我可以通过创建一个存储过程将数据插入到一个临时表中来实现这一点。然后我就可以使用"EXEC SP_Name"作为查询的报告向导了。然后我选择Body_part作为行,Incident_type作为列,并选择Totals作为数据。
CREATE TABLE #tmp
(
Body_part VARCHAR(200) NOT NULL,
Incident_type VARCHAR(250) NOT NULL,
)
INSERT INTO #tmp
SELECT ISNULL(Body_part, 'N/A'), ISNULL(Incident_type, 'N/A')
FROM [safety].vwIncomingPINS
WHERE submitted_on >= dateadd(year,-1,getdate()) AND submitted_on <=getdate()
INSERT INTO #tmp
SELECT ISNULL(Body_part, 'N/A'), Incident_type2
FROM [safety].vwIncomingPINS
WHERE submitted_on >= dateadd(year,-1,getdate()) AND submitted_on <=getdate() AND Incident_type2 IS NOT NULL
INSERT INTO #tmp
SELECT ISNULL(Body_part, 'N/A'), Incident_type3
FROM [safety].vwIncomingPINS
WHERE submitted_on >= dateadd(year,-1,getdate()) AND submitted_on <=getdate() AND Incident_type3 IS NOT NULL
SELECT Body_part, Incident_type, count(*) AS Totals from #tmp
GROUP BY Body_part, Incident_type