query to convert rows to columns in SQL

Question

this is my query:

with Q1 as
 (
     select *, 
         LAG(ignition) over (ORDER BY [timestamp]) as PrevStatus, 
         LEAD(ignition) over (order by [timestamp]) as NextStatus 
     FROM vehiclehistorytable
                            WHERE plateno='Crematory' and timestamp between '2021-09-02 00:00:10.00' and '2021-09-02 23:59:59.00'
),
 Q2 as
 (
     select *,
         ROW_NUMBER() over (order by timestamp) as rn,
         LAG([volume1]) OVER (ORDER BY [timestamp]) AS VolumeIgOff                    
        
            
     from  Q1
     where PrevStatus is null or NextStatus is null or not (ignition = PrevStatus and ignition = NextStatus)
 ),
 Q3 as
 (
     select a.timestamp, a.ignition, a.volume1
     from Q2 as a
     inner join Q2 as b on b.rn = a.rn + 1
     where a.rn % 2  0
 )
 select  
 timestamp AS IgnitionOn,
 ignition,
[volume1] AS FuelLevelON                        
 from Q3
 order by timestamp

this is my current results:

IgnitionOn                 ignition    FuelLevelON
2021-09-02 00:00:10.00      1             251.35
2021-09-02 00:39:09.00      0             251.35
2021-09-02 00:48:34.00      1             251.35
2021-09-02 03:24:31.00      0             208.1178
2021-09-02 03:37:22.00      1             208.1178
2021-09-02 04:59:37.00      0             181.4747
2021-09-02 05:17:14.00      1             181.4747
2021-09-02 06:20:27.00      0             159.8586
2021-09-02 06:22:29.00      1             159.8586
2021-09-02 07:30:41.00      0             140.2533
2021-09-02 11:29:38.00      1             141.2587
2021-09-02 12:41:52.00      0             117.6318
2021-09-02 12:43:22.00      1             117.6318
2021-09-02 14:07:43.00      0             254.3662
2021-09-02 14:23:49.00      1             254.3662
2021-09-02 15:31:14.00      0             238.2798
2021-09-02 15:41:02.00      1             238.2798
2021-09-02 16:46:55.00      0             218.6745
2021-09-02 16:48:25.00      1             218.6745
2021-09-02 17:55:06.00      0             196.5557
2021-09-02 18:09:03.00      1             196.5557
2021-09-02 19:09:37.00      0             174.9396
2021-09-02 19:30:26.00      1             174.9396
2021-09-02 20:56:53.00      0             152.8208
2021-09-02 21:01:40.00      1             152.8208
2021-09-02 22:08:59.00      0             135.729

this is my expected results:

IgnitionOn              IgnitionOff    
2021-09-02 00:00:10.00   2021-09-02 00:39:09.00
2021-09-02 00:48:34.00   2021-09-02 03:24:31.00
2021-09-02 03:37:22.00   2021-09-02 04:59:37.00
2021-09-02 05:17:14.00   2021-09-02 06:20:27.00
2021-09-02 06:22:29.00   2021-09-02 07:30:41.00
2021-09-02 11:29:38.00   2021-09-02 12:41:52.00
2021-09-02 12:43:22.00   2021-09-02 14:07:43.00 
2021-09-02 14:23:49.00   2021-09-02 15:31:14.00
2021-09-02 15:41:02.00   2021-09-02 16:46:55.00
2021-09-02 16:48:25.00   2021-09-02 17:55:06.00
2021-09-02 18:09:03.00   2021-09-02 19:09:37.00
2021-09-02 19:30:26.00   2021-09-02 20:56:53.00
2021-09-02 21:01:40.00   2021-09-02 22:08:59.00

this is my source data vehiclehistorytable:

timestamp               ignition       volume1   
2021-09-02 22:10:11.00      0          135.729  
2021-09-02 22:09:11.00      0          135.729  
2021-09-02 22:08:59.00      0          135.729  
2021-09-02 21:01:40.00      1          152.8208
2021-09-02 21:01:23.00      0          152.8208
2021-09-02 21:01:12.00      0          152.8208
2021-09-02 21:00:13.00      0          152.8208
2021-09-02 20:59:11.00      0          153.3235
2021-09-02 20:58:15.00      0          153.3235
2021-09-02 20:57:11.00      0          152.8208
2021-09-02 20:56:53.00      0          152.8208
2021-09-02 19:36:13.00      1          174.9396
2021-09-02 19:35:15.00      1          174.9396
2021-09-02 19:34:11.00      1          174.9396
2021-09-02 19:33:10.00      1          174.9396
2021-09-02 19:32:17.00      1          174.9396
2021-09-02 19:31:24.00      1          174.9396
2021-09-02 19:30:42.00      1          174.9396
2021-09-02 19:30:26.00      1          174.9396
2021-09-02 19:16:15.00      0          174.9396
2021-09-02 19:14:12.00      0          174.9396
2021-09-02 19:10:11.00      0          174.9396
2021-09-02 19:09:37.00      0          174.9396
2021-09-02 19:09:13.00      1          174.9396
2021-09-02 18:09:03.00      1          196.5557
2021-09-02 18:01:15.00      0          196.5557
2021-09-02 17:59:11.00      0          196.5557
2021-09-02 17:58:11.00      0          196.5557
2021-09-02 17:57:17.00      0          196.5557
2021-09-02 17:56:12.00      0          196.5557
2021-09-02 17:55:18.00      0          196.5557
2021-09-02 17:55:06.00      0          196.5557
2021-09-02 16:48:25.00      1          218.6745
2021-09-02 16:47:11.00      0          218.6745
2021-09-02 16:46:55.00      0          218.6745
2021-09-02 16:46:11.00      1          218.6745
2021-09-02 15:57:11.00      1          232.2474
2021-09-02 15:41:19.00      1          238.2798
2021-09-02 15:41:02.00      1          238.2798
2021-09-02 15:35:11.00      0          238.2798
2021-09-02 15:31:14.00      0          238.2798
2021-09-02 14:24:05.00      1          254.3662
2021-09-02 14:23:49.00      1          254.3662
2021-09-02 14:07:43.00      0          254.3662
2021-09-02 12:44:10.00      1          117.6318
2021-09-02 12:43:33.00      1          117.6318
2021-09-02 12:43:22.00      1          117.6318
2021-09-02 12:42:10.00      0          117.6318
2021-09-02 12:41:52.00      0          117.6318
2021-09-02 11:29:38.00      1          141.2587
2021-09-02 07:30:41.00      0          140.2533
2021-09-02 06:22:29.00      1          159.8586
2021-09-02 06:22:10.00      0          159.8586
2021-09-02 06:21:17.00      0          159.8586
2021-09-02 06:20:27.00      0          159.8586
2021-09-02 05:17:14.00      1          181.4747
2021-09-02 04:59:37.00      0          181.4747
2021-09-02 03:37:22.00      1          208.1178
2021-09-02 03:24:31.00      0          208.1178
2021-09-02 00:48:34.00      1          251.35   
2021-09-02 00:39:09.00      0          251.35   
2021-09-02 00:03:09.00      1          251.35   
2021-09-02 00:02:10.00      1          251.35   
2021-09-02 00:01:11.00      1          251.35   
2021-09-02 00:00:10.00      1          251.35

Please include some example source data too. The source doesn't have to be real, just enough to show the behaviours you're dealing with, and the actual/desired results need to match that example source data. https://stackoverflow.com/help/minimal-reproducible-example — MatBailie, Oct 17 '21 at 12:14
For example, can your source data ever have two sequential rows with the same ignition status? (on, on, off, off, etc?) — MatBailie, Oct 17 '21 at 12:17
@MatBailie my ignition status is the value of ignition column, 1 - Ignition On, 0 - Ignition Off — woo25, Oct 17 '21 at 23:48

MatBailie · Accepted Answer · 2021-10-18T10:43:06.593

That's a so-called gaps-and-islands problem.

As you only have two states, you can just look for transitions from 0 to 1. Summing that over time will give you a 'partition_id'; each partition beginning with consecutive 1's and ending with consecutive 0's.

Once you have those groups, conditional aggregation will find the first 1 in the group (switch on) and the first 0 in the group (switch off).

WITH
  transitions AS
(
  SELECT
    vehiclehistorytable.*,
    CASE WHEN LAG(ignition) OVER (ORDER BY timestamp) = ignition OR ignition = 0 THEN 0 ELSE 1 END   AS switch_on
  FROM
    vehiclehistorytable
),
  ignition_phases AS
(
  SELECT
    transitions.*,
    SUM(switch_on) OVER (ORDER BY timestamp)   AS partition_id
  FROM
    transitions
)
SELECT
  MIN(CASE WHEN ignition = 1 THEN timestamp END)   AS ignition_on,
  MIN(CASE WHEN ignition = 0 THEN timestamp END)   AS ignition_off
FROM
  ignition_phases
GROUP BY
  partition_id
ORDER BY
  partition_id

If you want to know the volumes at those times, add a check for when switch_off occurs, in the first cte...

WITH
  transitions AS
(
  SELECT
    vehiclehistorytable.*,
    CASE WHEN LAG(ignition) OVER (ORDER BY timestamp) = ignition OR ignition = 0 THEN 0 ELSE 1 END   AS switch_on,
    CASE WHEN LAG(ignition) OVER (ORDER BY timestamp) = ignition OR ignition = 1 THEN 0 ELSE 1 END   AS switch_off
  FROM
    vehiclehistorytable
),
  ignition_phases AS
(
  SELECT
    transitions.*,
    SUM(switch_on) OVER (ORDER BY timestamp)   AS partition_id
  FROM
    transitions
)
SELECT
  MIN(CASE WHEN switch_on  = 1 THEN timestamp END)   AS ignition_on,
  MIN(CASE WHEN switch_on  = 1 THEN volume    END)   AS ignition_on_volume,
  MIN(CASE WHEN switch_off = 1 THEN timestamp END)   AS ignition_off, 
  MIN(CASE WHEN switch_off = 1 THEN volume    END)   AS ignition_off_volume
FROM
  ignition_phases
GROUP BY
  partition_id
ORDER BY
  partition_id

EDIT: Inverted logic to deal with NULL coming from LAG()

Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=04a5ee6c62671cde5884a392831e0f16

thank you so much, it really helps a lot. As of now, my 1st row for ignition_on is null , when I want to know the volumes those times. — woo25, Oct 18 '21 at 04:54
@robs - You're welcome. Just remember to accept answers that solve your questions; that increases your reputation allowing you to upvote answers and questions, as well as increasing the chances that people engage with future questions of yours. — MatBailie, Oct 18 '21 at 16:42
Is it possible to set default datetime value if the value of column is null? SELECT ISNULL(MIN(CASE WHEN switch_on = 1 THEN timestamp END),DefaultDateTime) AS IgnitionOn, — woo25, Oct 20 '21 at 02:25
That should only happen for the first row *(if the time series starts at `off` there will be no `ignition_on` values in the first row)* or for the last row *(if the time series ends at `on` there will be no `ignition_off` values in the last row)*. You can certainly use `ISNULL()` or `COALESCE()` to fill those values in, though what values would make sense is a business-rules question you'd have to answer for yourself. — MatBailie, Oct 20 '21 at 10:13

query to convert rows to columns in SQL

1 Answers1