Pivot all Time Data on Date Column

Question

I need to create a report in SQL Server Reporting Service. The source table/query data is structured as follows:

 CNum |  EmpNo   |    TDate      | TimeIn    |  TimeOut 
  100 |  2       |   12/4/2019   |  7:00 AM  |  12:00 PM
  100 |  2       |   12/4/2019   |  12:30 PM |  3:30 PM
  100 |  2       |   12/5/2019   |  7:00 AM  |  12:00 PM
  100 |  2       |   12/5/2019   |  12:30 PM |  3:30 PM 

I need the report output to be displayed as follows (or something similar, just need to show the TDate as columns and any related time entries based on the CNum as rows).

 CNum |  12/4/2019 |   12/5/2019 | 
  100 |  7:00 AM   |   7:00 AM   | 
      |  12:00 PM  |   12:00 PM  |  
  100 |  12:30 PM  |   12:30 PM  | 
      |  3:30 PM   |   3:30 PM   | 

I have tried using the Matrix Tablix but this forces the group to only return on record per day, when there may be multiple. My goal is to write a SQL Query (CTE or PIVOT) which will give me the report data in the correct format so I will not have to get crazy in the report designer.

I am familiar with SQL but for some reason I cannot get any query to output (Pivot) and include both records for the day.

Any help/guidance will be much appreciated.

Answer 1

You can do this easily in SSRS with a small change to your dataset query.

I reproduced your sample data with the following

DECLARE @t TABLE(CNum int, EMpNo int, TDate Date, TimeIn Time, [Timeout] Time)
INSERT INTO @t VALUES
(100, 2, '2019/12/04', '07:00', '12:00'),
(100, 2, '2019/12/04', '12:30', '15:30'),
(100, 2, '2019/12/05', '07:00', '12:00'),
(100, 2, '2019/12/05', '12:30', '15:30')


SELECT *, ROW_NUMBER() OVER(PARTITION BY TDate, Cnum ORDER BY TimeIn) as RowN FROM @t 

Note: I added the RowN column which gives each row a unique number within each TDate and CNum. We add this to the CNum group in the matrix (so it groups by CNum then RowN)

Here's the final design including the row and column groups (Column group is just by TDate)

To get the 2nd row I right clicked the [TimeIn] 'cell' and did "Insert Row = > Inside Group - Below"

The final output looks like this