How to split results from one field into two separate fields in SQL Server 2014

Question

I am trying top generate a report that gives me the amount of traffic per hour, per lane, per direction.

The results I'm getting so far:

|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  |
---------------------------------------------

The results I would like to get:

|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 |
----------------------------------------

My approach so far was to UNION two sets of Code. One set for NB Direction and another set for SB direction.

My code so far:

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

That is obviously not the right approach, because it doesn't work.

What should my approach be so that I get a count for each direction like in the second table?

Answer 1

You can use pivot as below:

Select * from 
( Select transdate, [hour], lane, direction, [count] from #transdata ) a
pivot (max([count]) for Direction in ([NB],[SB])) p

Output as below:

+------------+------------------+------+----+----+
| 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 |
+------------+------------------+------+----+----+

If you have dynamic list of Direction you can use dynamic query as below:

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