Pivot Table not getting proper output in SQL Server 2012

Question

**I want Output like this**this is the output i needed that show location and total count of count with time difference

From the below output picture in this output it showing location and total count of count with time difference

select * from(select Location,Count(Location) as [Total Count],Time from 
( select     TM.Tranaction_Slno,Container_Number,CFM.CFS_Name,
TD.Container_Size,TM.Created_On,Read_Time,gate,Location, Trailer_RegNo,
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)/3600)+':'+
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)%3600/60)+':'+
convert(varchar(5),(DateDiff(s, TM.Created_On, Read_Time)%60))  timeDiff,
convert(varchar(5),DateDiff(hh, TM.Created_On, Read_Time)%60) [Time]
from Transaction_Master TM
inner join Transaction_Data TD on TD.Tranaction_Slno = TM.Tranaction_Slno
left join Transaction_Track TT on TT.Transaction_Slno = TM.Tranaction_Slno
inner join CFSMaster CFM on TM.CFS_ID = CFM.CFS_Id
inner join TrailerMaster TMM on TM.Trailer_ID= TMM.Trailer_ID
where TM.Created_On between '2018-01-01 00:00:00.000' and '2019-02-01 00:59:59.000'
and IE_STATUS=1 and CCTLCITPL_Statis=1
group byconvert(varchar(5),DateDiff(hh, TM.Created_On, Read_Time)%60),
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)/3600)+':'+
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)%3600/60)+':'+
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)%60) 
,CFM.CFS_Name,TM.Tranaction_Slno,Container_Number,CFM.CFS_Name,
TD.Container_Size,gate,Location,Read_Time,TM.Created_On,Trailer_RegNo )as 
CCR 
group by Location,Time )as tt 
pivot (
count([Total Count])                                                                                                                                
for Location in(custom,zero)
) as pvt 

Above query i wrote but couldn't getting proper output

Some Sample Data is below

Tranaction_Slno Container_Number    CFS_Name    Container_Size  Created_On                  Read_Time       gate    Location    Trailer_RegNo   timeDiff    Time
1306886         TGHU4478377 ALL CARGO LOGISTIC  40Ft       2018-08-21 21:02:47.393  2018-08-21 22:49:20.430 ZEROINLANEHONE  ZERO        TN28BB8404  1:46:33     0:0:1
1563890     MEDU8790500 CENTRAL WAREHOUSING     40Ft       2018-12-17 16:05:34.637  2018-12-17 17:52:07.427 ZEROINLANEF ZERO        TN28AE0543  1:46:33     0:0:1
1607008     SEGU5209498 CENTRAL WAREHOUSING     40Ft       2019-01-11 18:10:31.127  2019-01-11 19:57:15.493 ZEROINLANEG ZERO        TN18AJ9559  1:46:44     0:0:1

Answer 1

You can try below -

With cte as
(
select Location,Count(Location) as [Total Count],Time from 
( select     TM.Tranaction_Slno,Container_Number,CFM.CFS_Name,
TD.Container_Size,TM.Created_On,Read_Time,gate,Location, Trailer_RegNo,
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)/3600)+':'+
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)%3600/60)+':'+
convert(varchar(5),(DateDiff(s, TM.Created_On, Read_Time)%60))  timeDiff,
convert(varchar(5),DateDiff(hh, TM.Created_On, Read_Time)%60) [Time]
from Transaction_Master TM
inner join Transaction_Data TD on TD.Tranaction_Slno = TM.Tranaction_Slno
left join Transaction_Track TT on TT.Transaction_Slno = TM.Tranaction_Slno
inner join CFSMaster CFM on TM.CFS_ID = CFM.CFS_Id
inner join TrailerMaster TMM on TM.Trailer_ID= TMM.Trailer_ID
where TM.Created_On between '2018-01-01 00:00:00.000' and '2019-02-01 00:59:59.000'
and IE_STATUS=1 and CCTLCITPL_Statis=1
group byconvert(varchar(5),DateDiff(hh, TM.Created_On, Read_Time)%60),
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)/3600)+':'+
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)%3600/60)+':'+
convert(varchar(5),DateDiff(s, TM.Created_On, Read_Time)%60) 
,CFM.CFS_Name,TM.Tranaction_Slno,Container_Number,CFM.CFS_Name,
TD.Container_Size,gate,Location,Read_Time,TM.Created_On,Trailer_RegNo )as 
CCR group by Location,Time
)

select * from
(
select location, [total count],time,'p'+location as location1
from cte
)A pivot
(
count([total count]) for Location in(custom,zero)
) as p1
pivot
(
count(time) for location1 in (custom,zero)
) as p2