title: Pivot Table not getting proper output in SQL Server 2012
body: <pre><code>**I want Output like this**this is the output i needed that show location and total count of count with time difference
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/wpkFh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wpkFh.png" alt="enter image description here"></a></p>
  
  <p><strong>From the below output picture</strong>
  <a href="https://i.stack.imgur.com/GvuEp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GvuEp.png" alt="enter image description here"></a>
  in this output it showing location and total count of count with time difference</p>
  
  <pre><code>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 
  </code></pre>
  
  <p><strong>Above query i wrote but couldn't getting proper output</strong></p>
  
  <p><strong>Some Sample Data is below</strong></p>
  
  <pre><code>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
  </code></pre>