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

Question

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

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

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.

So you want to pivot your data, I’m pretty sure the solution depends on what database you are using. Please tag the question with the used DBMS. — Joakim Danielson, Dec 09 '18 at 08:14
What concrete **RDBMS** is this for? **SQL** is just the query language and many features go way beyond the SQL Standard and are vendor-specific — marc_s, Dec 09 '18 at 08:16
@JoakimDanielson I have SQL Server 2016 SP1 native (I believe tabular install), SSMS 2017, SSRS 2016. Yes, essentially I am wanting to create a static, printable report with the table laid out in the image above which pivots some of the data (importantly, it has to be in the order of Start/Process/Stop please. — Jimbo, Dec 09 '18 at 08:40
In your sample data, for groupId 2 there's 2 start, 2 process and 2 stop is it expected or is it a typo ? What would you expect to see in this case ? — Eponyme Web, Dec 09 '18 at 09:27

score 1 · Accepted Answer · answered Dec 09 '18 at 09:45

1

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.

answered Dec 09 '18 at 09:45

Eponyme Web

966
5
10

The Start refers to opening a valve and the time it takes to do so. Process is allowing x amount of liquid through and the time it took. End is how long it takes the valve to close. I'll give the above that a try and report back. Thanks, Eponyme Web – Jimbo Dec 09 '18 at 10:21
The query above returns an error of 'Invalid object name 'PROCESS_LOG'. Could not update a list of fields for the query. Verify that you can connect to the data source and syntax is correct. – Jimbo Dec 09 '18 at 10:47
@Jimbo You didn't give us the table name, I used PROCESS_LOG but you'll have to replace it (3 times) with the real table name. Same thing for the columns, I did the best I could with the information available. – Eponyme Web Dec 09 '18 at 10:59
Apologies, I'm new to this. Ok, I changed the table name and it works a treat, many thanks. One last question, am I able to add parameters to this as normal or will it fall over. I want the user to be able to filter on the date element of the datetime (which I know how to do) – Jimbo Dec 09 '18 at 11:13
@Jimbo you can add parameters, bt contains the info for the Start, pt for the Process ans st for the Stop. For instance to check which valves took more than 1min to close you'd add AND st.TotalEventDuration > '00:01:00' – Eponyme Web Dec 09 '18 at 11:41
I find it amusing that this answer was accepted because I don't think it does much useful with the sample data the OP has shown. – Gordon Linoff Dec 09 '18 at 12:56

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

1 Answers1