[Solved] How to transform this raw table with SQL?

by


Here is a way, demo at SQL Fiddle

CREATE TABLE Table1
    ("id" int, "datetime" time, "name" varchar(1))
;

INSERT INTO Table1
    ("id", "datetime", "name")
VALUES
    (1, '11:10:01', 'A'),
    (1, '11:10:02', 'A'),
    (1, '11:10:05', 'B'),
    (1, '11:12:02', 'A'),
    (1, '11:12:10', 'A'),
    (2, '11:13:02', 'B'),
    (2, '11:13:06', 'A'),
    (1, '11:14:01', 'A'),
    (1, '11:14:02', 'B'),
    (1, '11:14:05', 'A')
;

Query 1:

with cte as (
  select
     id, datetime, name
  , row_number() over(partition by id, date_trunc('minute',datetime) order by datetime) rn
  , lead(name) over(partition by id, date_trunc('minute',datetime) order by datetime) lead_name
  from table1
  )
, cte2 as (
    select
    id, date_trunc('minute',datetime) t, concat(name,lead_name) pair
    from cte
    where lead_name IS NOT NULL
   )
select
       id
     , t
     , count(case when pair="AA" then 1 end) as AA
     , count(case when pair="AB" then 1 end) as AB
     , count(case when pair="BA" then 1 end) as BA
     , count(case when pair="BB" then 1 end) as BB
from cte2
group by
       id
     , t
order by
       id
     , t
;

Results:

| id |                                                t | aa | ab | ba | bb |
|----|--------------------------------------------------|----|----|----|----|
|  1 | 0 years 0 mons 0 days 11 hours 10 mins 0.00 secs |  1 |  1 |  0 |  0 |
|  1 | 0 years 0 mons 0 days 11 hours 12 mins 0.00 secs |  1 |  0 |  0 |  0 |
|  1 | 0 years 0 mons 0 days 11 hours 14 mins 0.00 secs |  0 |  1 |  1 |  0 |
|  2 | 0 years 0 mons 0 days 11 hours 13 mins 0.00 secs |  0 |  0 |  1 |  0 |

Query v2:

with cte as (
  select
     id, datetime, name
  , row_number() over(partition by id order by datetime) rn
  , lead(name) over(partition by id order by datetime) lead_name
  from table1
  )
, cte2 as (
    select
    id, date_trunc('minute',datetime) t, concat(name,lead_name) pair
    from cte
    where lead_name IS NOT NULL
   )
select
       id
     , t
     , count(case when pair="AA" then 1 end) as AA
     , count(case when pair="AB" then 1 end) as AB
     , count(case when pair="BA" then 1 end) as BA
     , count(case when pair="BB" then 1 end) as BB
from cte2
group by
       id
     , t
order by
       id
     , t
;

Results v2:

| id |                                                t | aa | ab | ba | bb |
|----|--------------------------------------------------|----|----|----|----|
|  1 | 0 years 0 mons 0 days 11 hours 10 mins 0.00 secs |  1 |  1 |  1 |  0 |
|  1 | 0 years 0 mons 0 days 11 hours 12 mins 0.00 secs |  2 |  0 |  0 |  0 |
|  1 | 0 years 0 mons 0 days 11 hours 14 mins 0.00 secs |  0 |  1 |  1 |  0 |
|  2 | 0 years 0 mons 0 days 11 hours 13 mins 0.00 secs |  0 |  0 |  1 |  0 |

1

solved How to transform this raw table with SQL?