Search code examples
sqlpivotsql-server-2016ssrs-2016ssms-17

SQL query: using data in a column as column names in another table?

I am struggling to get data from within a table that looks like this:

Data Example

to display horizontally, grouped by GroupID, then the start/process/stop times (in that order) to display on a single row like this:

enter image description here

I have tried writing several SQL case statements but I cant get it to work. I followed some advice online saying to write an IIF expression in a table in SSRS Report Builder but I couldn't get that to work. Any help would be great.

Solution

  • Using plain SQL I think this is the query you're looking for

    SELECT
    bt.GroupID as 'ID',
    bt.EventStartTime as 'StartStartTime',
    bt.EventEndTime as 'StartEndTime',
    bt.TotalEventDuration as 'StartDuration',
    pt.EventStartTime as 'ProcessStartTime',
    pt.EventEndTime as 'ProcessEndTime',
    pt.TotalEventDuration as 'ProcessDuration',
    st.EventStartTime as 'StopStartTime',
    st.EventEndTime as 'StopEndTime',
    st.TotalEventDuration as 'StopDuration'
FROM
    PROCESS_LOG bt
    LEFT JOIN PROCESS_LOG pt ON
        bt.GroupID = pt.GroupID
    LEFT JOIN PROCESS_LOG st ON
        bt.GroupID = st.GroupID
WHERE
    bt.Event = 'Start'
AND pt.Event = 'Process'
AND st.Event = 'Stop'

    I'm assuming that you have to start in order to process and/or stop which doesn't seem unrealistic. The idea is to join the table 3 times to extract the info for each event, GroupID being the 'glue' to relate them.