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.

score 1 · Answer 1 · answered Jan 16 '20 at 15:31

1

I think, you may take it fwd from Efficiently convert rows to columns in sql server

answered Jan 16 '20 at 15:31

lije

420
2
15

score 1 · Answer 2 · answered Jan 16 '20 at 15:44

1

Here is the answer :

With CTE as (
    Select
        CNum,
        TDate,
        TimeIn as [Time],
        'In' as [Action]
    From TimeTable
    Union All
    Select
        CNum,
        TDate,
        [TimeOut] as [Time],
        'Out' as [Action]
    From TimeTable
)
Select
    *
From CTE
Pivot(min([Time]) for TDate in ([2019-12-04],[2019-12-05])) as pivot_table
union all
Select
    *
From CTE
Pivot(max([Time]) for TDate in ([2019-12-04],[2019-12-05])) as pivot_table

answered Jan 16 '20 at 15:44

XAMT

1,515
2
11
31

This looks like it would work, however the data in the table and TDate field is dynamic (always growing). I cannot hard code the "IN" clause as shown above. Is there a method to dynamically do the following: Pivot(min([Time]) for TDate in ([2019-12-04],[2019-12-05])) – gdex Jan 16 '20 at 15:57
Generating dynamic data (different column names for each run) is not possible in a database view. so you can change it to a stored procedure. – XAMT Jan 16 '20 at 16:01
guide to dynamic SQL: https://stackoverflow.com/questions/39313748/dynamic-sql-server-query?rq=1 – XAMT Jan 16 '20 at 16:03

score 1 · Accepted Answer · answered Jan 16 '20 at 16:12

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

Absolutely what i needed!!!! Thank you so much. The RowN is the key. — gdex, Jan 16 '20 at 16:53

Pivot all Time Data on Date Column

3 Answers3