AkAlan
asked on
Returning Record Count in Pivot Table
I have a stored procedure which returns data using a PIVOT statement. I need to modify the statement so it returns a record count along with the row of data but am not able to comprehend the concept. Attached is an example of what I have. It returns data like so:
EquipmentGroup Urgent Routine
Radars 100.00 75.00
Radios 50.00 75.00
What I need now is an output which sums up the number or requisitions that make up each EquipmentGroup by Priority like so:
EquipmentGroup Urgent #UrgentReqs Routine #RoutineReqs
Radars 100.00 3 75.00 4
Radios 50.00 2 75.00 2
I have played around with what I have quite a bit but am missing some key concept. Any help would be appreciated.
EquipmentGroup Urgent Routine
Radars 100.00 75.00
Radios 50.00 75.00
What I need now is an output which sums up the number or requisitions that make up each EquipmentGroup by Priority like so:
EquipmentGroup Urgent #UrgentReqs Routine #RoutineReqs
Radars 100.00 3 75.00 4
Radios 50.00 2 75.00 2
I have played around with what I have quite a bit but am missing some key concept. Any help would be appreciated.
SELECT EquipmentGroup,
ISNULL(U, 0) AS Urgent,
ISNULL(R, 0) AS Routine
FROM
(
SELECT
r.ImmsEquipmentGroup,
r.Priority,
Sum(r.TOTAL_EST_REQ_AMT) AS Price,
FROM
Requisitions r
GROUP BY EquipmentGroup,r.Priority
) Data
PIVOT (SUM(Price) FOR Priority IN(U,R)) pvt
ORDER BY EquipmentGroup
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try this
SELECT
pk.EquipmentGroup,
ISNULL(pv.U,0)as Urgent,
pk.R AS [#UrgentReqs] ,
ISNULL(pv.R,0) as Routine,
pk.U AS [#RoutineReqs]
FROM
(SELECT EquipmentGroup,
Priority ,
SUM(TOTAL_EST_REQ_AMT) AS Price
FROM Requisitions group by EquipmentGroup, Priority) p
PIVOT (SUM(Price) FOR Priority in (U, R)) as pv
INNER JOIN
(SELECT DISTINCT EquipmentGroup,
Priority ,
TOTAL_EST_REQ_AMT
FROM Requisitions) p
PIVOT ( COUNT (TOTAL_EST_REQ_AMT) FOR Priority in (U, R)) as pk
Can you provide some sample data from your table?
ASKER
Worked perfectly thanks
Change
select
pvt.EquipmentGroup
, pvt.E
, pvt.R
, c.E
, c.R
to
select
pvt.EquipmentGroup
, pvt.E
, pvt.R
, c.E + c.R