SQL YYMM 分组的两种方法

-- 创建一个 View，辅助一下会简单很多

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_CasePhysicianProcedure]'))

DROP VIEW [dbo].[V_CasePhysicianProcedure]

GO

CREATE VIEW V_CasePhysicianProcedure

as

select

--cc.Station_ID, cc.Case_No,

cc.Station_ID + RIGHT('00000' + CAST(cc.Case_No as nvarchar(100)), 5) as CaseNo,

cc.Patient_ID as PatientID, cc.Last_Name as PatientName,

cc.Procedure_Date as ProcedureDate, CONVERT(nvarchar(4), cc.Procedure_Date, 12) as YYMM,

rr.User_ID as PhysicianID, rr.CR_Resource_Name as PhysicianName,

pp.Procedure_ID as ProcedureID, pp.CP_Procedure_Name as ProcedureName

from Cases as cc

left join Case_Resources as rr on

cc.Station_ID = rr.Station_ID and cc.Case_No = rr.Case_No

and rr.Active = 1 and rr.Resource_ID = 0

left join Case_Procedures as pp on

cc.Station_ID = pp.Station_ID and cc.Case_No = pp.Case_No

and pp.Active = 1

--where Procedure_Date between '2021-01-01' and '2021-12-31'

GO

-- 检查一下这个 View

select * from V_CasePhysicianProcedure

-- 方法一

select * from

(

select PhysicianID, PhysicianName, ProcedureID, ProcedureName, YYMM, CaseNo

from V_CasePhysicianProcedure

where ProcedureDate between '2021-01-01' and '2021-12-31'

) as aa

pivot (

count(CaseNo) for YYMM in ([2101], [2102], [2103], [2104], [2105], [2106], [2107], [2108], [2109], [2110], [2111], [2112])

) as bb

-- 方法二

select PhysicianID, PhysicianName, ProcedureID,ProcedureName,

count(case when YYMM = 2101 then ProcedureID else null end) as [2101],

count(case when YYMM = 2102 then ProcedureID else null end) as [2102],

count(case when YYMM = 2103 then ProcedureID else null end) as [2103],

count(case when YYMM = 2104 then ProcedureID else null end) as [2104],

count(case when YYMM = 2105 then ProcedureID else null end) as [2105],

count(case when YYMM = 2106 then ProcedureID else null end) as [2106],

count(case when YYMM = 2107 then ProcedureID else null end) as [2107],

count(case when YYMM = 2108 then ProcedureID else null end) as [2108],

count(case when YYMM = 2109 then ProcedureID else null end) as [2109],

count(case when YYMM = 2110 then ProcedureID else null end) as [2110],

count(case when YYMM = 2111 then ProcedureID else null end) as [2111],

count(case when YYMM = 2112 then ProcedureID else null end) as [2112]

from V_CasePhysicianProcedure

where ProcedureDate between '2021-01-01' and '2021-12-31'

group by PhysicianID, PhysicianName, ProcedureID, ProcedureName