我正在尝试生成一个报告,该报告可以为我提供每小时,每个车道,每个方向的流量。
到目前为止,我得到的结果是:
|TransDate |Hour |Lane| Direction| Count|
---------------------------------------------
|2017-09-05 |1:00 | 1 | NB | 18 |
---------------------------------------------
|2017-09-05 |1:00 | 1 | SB | 12 |
---------------------------------------------
|2017-09-05 |1:00 | 2 | NB | 42 |
---------------------------------------------
|2017-09-05 |1:00 | 2 | SB | 31 |
---------------------------------------------
|2017-09-05 |1:00 | 3 | NB | 7 |
---------------------------------------------
|2017-09-05 |1:00 | 3 | SB | 8 |
---------------------------------------------
我想要得到的结果:
|TransDate |Hour |Lane| NB | SB |
----------------------------------------
|2017-09-05 |1:00 | 1 | 18| 12 |
----------------------------------------
|2017-09-05 |1:00 | 2 | 42| 31 |
----------------------------------------
|2017-09-05 |1:00 | 3 | 7| 8 |
----------------------------------------
到目前为止,我的方法是将UNION分为两组代码。一组用于NB方向,另一组用于SB方向。
到目前为止,我的代码:
SELECT CAST(TransDT as DATE) as 'TransDate'
,CAST(DATEPART(Hour, TransDT) as varchar) + ':00' as 'Hour'
,LaneID
,Direction
,COUNT(*) as 'NB_Count'
FROM Traffic_analysis
WHERE cast(TransDT as date) = DATEADD (DAY, -1 , cast(SYSDATETIME() as date))
AND Direction = 'NB'
GROUP BY CAST(TransDT as DATE), DATEPART(Hour, TransDT), LaneID, Direction
ORDER BY CAST(TransDT as DATE), DATEPART(Hour, TransDT), LaneID, Direction
这显然不是正确的方法,因为它不起作用。
我的方法应该是什么,以便像第二张表中那样对每个方向进行计数?
您可以按以下方式使用数据透视:
Select * from
( Select transdate, [hour], lane, direction, [count] from #transdata ) a
pivot (max([count]) for Direction in ([NB],[SB])) p
输出如下:
+------------+------------------+------+----+----+
| transdate | hour | lane | NB | SB |
+------------+------------------+------+----+----+
| 2017-09-05 | 01:00:00.0000000 | 1 | 18 | 12 |
| 2017-09-05 | 01:00:00.0000000 | 2 | 42 | 31 |
| 2017-09-05 | 01:00:00.0000000 | 3 | 7 | 8 |
+------------+------------------+------+----+----+
如果您具有动态的“方向”列表,则可以如下使用动态查询:
Declare @cols1 varchar(max)
Declare @query nvarchar(max)
Select @cols1 = stuff((select Distinct ','+QuoteName(Direction) from #transdata for xml path('')),1,1,'')
Select @query = ' Select * from
( Select transdate, [hour], lane, direction, [count] from #transdata ) a
pivot (max([count]) for Direction in (' + @cols1 + ')) p '
Exec sp_executeSql @query
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句