sql - 数据透视表在 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

从下面的输出图片 中，它显示了位置和计数的总计数与时间差

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 

上面的查询我写但无法得到正确的输出

一些示例数据如下

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

标签： sqlsql-serversql-server-2012

Answer 1

你可以试试下面 -

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