SQL - Postgresql - Pivot

Question

I need to pivot a table with the last 2 records from a fk with PostgreSQL:

Currently I have one table with this structure:

id | fk | start_date | end_date   | value 
---------------------|------------|-------
01 | 01 | 2017-10-01 | 2017-11-01 | 1
02 | 01 | 2017-12-10 | 2018-01-10 | 9
03 | 01 | 2018-01-10 | 2018-02-10 | 2
04 | 02 | 2017-11-10 | 2017-12-10 | 1
05 | 02 | 2017-12-10 | 2018-01-10 | 2
06 | 03 | 2017-12-10 | 2018-01-10 | 8

I want to output with this structure:

fk | start_date_1 | end_date_1 | value_1 | start_date_2 | end_date_2 | value_2 
---|--------------|------------|---------|--------------|------------|---------
01 | 2018-01-10   | 2018-02-10 | 2       | 2017-12-10   | 2018-01-10 | 9
02 | 2017-12-10   | 2018-01-10 | 2       | 2017-11-10   | 2017-12-10 | 1
03 | 2017-12-10   | 2018-01-10 | 8       | NULL         | NULL       | NULL

I need a code that can be used by to search last 2 records and last 24 records.

light souls · Accepted Answer · 2018-08-01 08:38:09Z

1

If I understood you correctly, it can be achieved also using LATERAL join;

For each value from t1 's fk, LATERAL join searches second value from t2.

select t1.fk,
   t1.start_date as start_date_1,
   t1.end_date as end_date_1,
   t1._value as _value1,
   _second.* 
from
 (
--get first record of last two
select distinct on (dt.fk) dt.fk,dt.start_date,dt.end_date,dt._value
from (
    --last 24 records
    select * from table1 order by start_date DESC limit 24
     ) dt
order by dt.fk,dt.start_date DESC
) t1
LEFT JOIN LATERAL(
--get second record of last two
select start_date as start_date_2,
       end_date as end_date_2,
       _value as _value2
from table1 as t2
where t2.fk = t1.fk and 
t1.start_date > t2.start_date --to get second value
order by t2.start_date desc
limit 1) _second on true;

answered Aug 1, 2018 at 8:38

light souls

7381 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

riskop · Accepted Answer · 2017-12-12 08:08:32Z

You can do this with the help of analytic / window functions.

A working solution with temporary table for the last 2 records case:

create test data:

drop table testdata;
create table testdata(id integer, fk integer, start_date date, end_date date, value integer);

insert into testdata values(1, 1, '2017-10-01', '2017-11-01', 1);
insert into testdata values(2, 1, '2017-12-10', '2018-01-10', 9);
insert into testdata values(3, 1, '2018-01-10', '2018-02-10', 2);
insert into testdata values(4, 2, '2017-11-10', '2017-12-10', 1);
insert into testdata values(5, 2, '2017-12-10', '2018-01-10', 2);
insert into testdata values(6, 3, '2017-12-10', '2018-01-10', 8);

create a helper table, ranked by date, then select the pivot table from it:

WITH ranked AS (
SELECT id, fk, start_date, end_date,value, RANK() OVER (PARTITION BY fk ORDER BY start_date desc) from testdata
)
select fk, 
(select start_date as start_date_1 from ranked where rank=1 and ranked.fk=testdata.fk),
(select end_date   as end_date_1   from ranked where rank=1 and ranked.fk=testdata.fk),
(select value      as value_1      from ranked where rank=1 and ranked.fk=testdata.fk),
(select start_date as start_date_2 from ranked where rank=2 and ranked.fk=testdata.fk),
(select end_date   as end_date_2   from ranked where rank=2 and ranked.fk=testdata.fk),
(select value      as value_2      from ranked where rank=2 and ranked.fk=testdata.fk)
from testdata group by fk order by fk;

result:

fk           start_date_1   end_date_1     value_1      start_date_2   end_date_2     value_2      
---------------------------------------------------------------------------------------------------
1            2018-01-10     2018-02-10     2            2017-12-10     2018-01-10     9            
2            2017-12-10     2018-01-10     2            2017-11-10     2017-12-10     1            
3            2017-12-10     2018-01-10     8            <null>         <null>         <null>

IMHO your approach is unpractical for the last 24 records case. Would you really like to process a table with 24*3 + 1 column?

If I were you, I'd create a ranked table / result for the last 24 records and would process that on the application side. Query for the last 24:

select * from (
select id, fk, start_date, end_date, value, rank() OVER (PARTITION BY fk ORDER BY start_date desc) from testdata
) as r where rank < 25;

Collectives™ on Stack Overflow

SQL - Postgresql - Pivot

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related