Wednesday, 24 August 2016

Multiple Pivots in Sql Server

CREATE TABLE TblPivot
(
Time_Dt Date,
Workflow_Id int not null identity(1,1),
Completed_No int,
Average Numeric(6,2)
)


insert into TblPivot
(Time_Dt,Completed_No,Average) values
('2016-08-24',null,1.5)

insert into TblPivot
(Time_Dt,Completed_No,Average) values
('2016-08-24',null,1.6)


insert into TblPivot
(Time_Dt,Completed_No,Average) values
('2016-08-24',null,2.6)

insert into TblPivot
(Time_Dt,Completed_No,Average) values
('2016-08-24',100,2.9)






Select Time_Dt,sum(Coalesce(([10004]),0)) Completed,
sum(Coalesce(([1]),0)) Workflow_1,
sum(Coalesce(([2]),0)) Workflow_2,
sum(Coalesce(([3]),0)) Workflow_3,
sum(Coalesce(([4]),0)) Workflow_4
from 
(SELECT
*
 FROM
 (
    SELECT
Time_Dt,Workflow_Id,
case when Completed_No is not null then 10000+Workflow_Id end Count_Workflow_Id,
Completed_No,Average
 FROM TblPivot
 ) AS P
 

PIVOT
(
  Sum(Completed_No) FOR Count_Workflow_Id IN ([10004])
) AS pv1
 
-- For ItemName
PIVOT
(
 Sum(Average) FOR  Workflow_Id in ([1], [2], [3],[4])
) AS pv2 )a group by Time_Dt


--

Regards,
Ashif Ahamed 
 


"We can't solve problems by using the same kind of thinking we used when we created them - Albert Einstein"

No comments:

Post a Comment