0
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"
0Awesome Comments!