SELECT *
FROM (SELECT P.IncidentID,
PT.Penalty,
I.Title,
COUNT(P.PenaltyID) as Penaltycount
FROM
PenaltyMaster P
INNER JOIN PenaltyTypeMaster PT ON P.PenaltyTypeID = PT.PenaltyTypeID
LEFT JOIN IncidentsMaster I ON P.IncidentID = I.IncidentID
WHERE
ISNULL(P.IsDeleted, 0) = 0
GROUP BY P.IncidentID,
PT.Penalty,
I.Title) AS PenaltycountData
PIVOT( SUM([Penaltycount])
FOR Penalty IN ([OneTime],[Recurring])) AS PenaltycountPivot
use sqlteam
go
create table #IncidentsMaster(IncidentID int, Title nvarchar(150))
insert into #IncidentsMaster
select 1, 'Light Saber Light too short' union
select 2, 'Warp Speed Actuator Malfunction'
select * From #IncidentsMaster
create table #PenaltyTypeMaster(PenaltyTypeID int, Penalty nvarchar(150))
insert into #PenaltyTypeMaster
select 1, 'OneTime' union
select 2, 'Recurring'
select * from #PenaltyTypeMaster
create table #PenaltyMaster(PenaltyID int identity(1,1), IncidentID int, PenaltyTypeID int, IsDeleted bit default((0)))
insert into #PenaltyMaster(IncidentID,PenaltyTypeID)
select 1, 1 union
select 2, 1 union
select 2, 2
SELECT IncidentID
, Title
, OneTime = coalesce(OneTime, 0)
, Recurring = coalesce(Recurring, 0)
FROM (SELECT P.IncidentID,
PT.Penalty,
I.Title,
COUNT(P.PenaltyID) as Penaltycount
FROM
#PenaltyMaster P
INNER JOIN #PenaltyTypeMaster PT ON P.PenaltyTypeID = PT.PenaltyTypeID
LEFT JOIN #IncidentsMaster I ON P.IncidentID = I.IncidentID
WHERE
ISNULL(P.IsDeleted, 0) = 0
GROUP BY P.IncidentID,
PT.Penalty,
I.Title) AS PenaltycountData
PIVOT( SUM([Penaltycount])
FOR Penalty IN ([OneTime],[Recurring])) AS PenaltycountPivot