CODEWITHSUNDEEP
sqlpivotfirebirdfirebird2.5
jeffers
jeffers

Reputation: 145

How to show columns in row on firebird2.5?

I have one table and I need to do sql query which convert rows on columns. My table looks like this:

    ----------------------------------------------------------
    Id |IdR |DateFrom    |DateTo      |NameOfHappening |Amount

    293|264 |01.06.2019  |30.06.2019  |Disease 1       |2836.80
    369|600 |01.07.2019  |07.07.2019  |Disease 1       |661.92
    646|631 |01.08.2019  |11.08.2019  |Disease 1       |876.59
    647|631 |12.08.2019  |21.08.2019  |Disease 2       |796.90
    840|703 |30.09.2019  |30.09.2019  |Disease 1       |90.75
    971|718 |31.05.2019  |31.05.2019  |Disease 1       |0.00

I wrote query which show date but I have problem with other columns. My SQL query looks like this:

select
a.IDR,
(select max(op.DATE_FROM) from HAPP_TABLE op where op.IDR=a.IDR) as DATE_FROM_HP1,
(select max(op.DATE_TO) from HAPP_TABLE op where op.IDR=a.IDR) as DATE_TO_HP1,
(select max(op.DATE_FROM) from HAPP_TABLE op where op.IDR=a.IDR group by op.DATE_FROM order by op.DATE_FROM desc rows 2 to 2) as DATE_FROM_HP2,
(select max(op.DATE_TO) from HAPP_TABLE op where op.IDR=a.IDR group by op.DATE_FROM order by op.DATE_FROM desc rows 2 to 2) as DATE_TO_HP2,
(select max(op.DATE_FROM) from HAPP_TABLE op where op.IDR=a.IDR group by op.DATE_FROM order by op.DATE_FROM desc rows 3 to 3) as DATE_FROM_HP3,
(select max(op.DATE_TO) from HAPP_TABLE op where op.IDR=a.IDR group by op.DATE_FROM order by op.DATE_FROM desc rows 3 to 3) as DATE_TO_HP3
from HAPP_TABLE a
group by a.IDR

To this column date I need to connect other data from column NameOfHappening and Amount in one row group by IDR column. But data from this two column I can't get like date query date. I have to select this column to row that corresponding to dates. I want to get something like this:

IdR  DATE_FROM_HP1  DATE_TO_HP1   DATE_FROM_HP2  DATE_TO_HP2

264 |01.06.2019    |30.06.2019    |Disease 1|null           |null       |null
600 |01.07.2019    |07.07.2019    |Disease 1|null           |null       |null
631 |12.08.2019    |21.08.2019    |Disease 2|01.08.2019     |11.08.2019 |Disease 1
703 |30.09.2019    |30.09.2019    |Disease 1|null           |null       |null
718 |31.05.2019    |31.05.2019    |Disease 1|null           |null       |null

Please help me with this problem because I try a few solution and each ended with a multiple row selection message.

Upvotes: 0

Views: 118

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109014

A query like select max(op.DATE_FROM) from HAPP_TABLE op where op.IDR=a.IDR only has one row, so the addition of the rows 2 to 2 or rows 3 to 3 is not going to produce values.

In addition, your usage of max will produce values that don't necessarily belong together, so it might produce the wrong result

You can change your existing query by removing the use of max, applying a consistent order by and fetching specific rows, eg:

select
a.IDR,
(select op.DATE_FROM from HAPP_TABLE op where op.IDR=a.IDR order by op.DATE_FROM rows 1) as DATE_FROM_HP1,
(select op.DATE_TO from HAPP_TABLE op where op.IDR=a.IDR order by op.DATE_FROM rows 1) as DATE_TO_HP1,
(select op.DATE_FROM from HAPP_TABLE op where op.IDR=a.IDR order by op.DATE_FROM desc rows 2 to 2) as DATE_FROM_HP2,
(select op.DATE_TO from HAPP_TABLE op where op.IDR=a.IDR order by op.DATE_FROM desc rows 2 to 2) as DATE_TO_HP2,
(select op.DATE_FROM from HAPP_TABLE op where op.IDR=a.IDR order by op.DATE_FROM desc rows 3 to 3) as DATE_FROM_HP3,
(select op.DATE_TO from HAPP_TABLE op where op.IDR=a.IDR order by op.DATE_FROM desc rows 3 to 3) as DATE_TO_HP3
from HAPP_TABLE a
group by a.IDR

However, the solution suggested by Gordon is probably better.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Use a correlated subquery to get the "previous" row and then join back:

select h.*, hprev.*  -- select whatever columns you want
from (select h.*,
             (select max(h2.date)
              from happ_table h2
              where h2.idr = h.idr and
                    h2.date_from < h.date_from
             ) as prev_date_from
      from happ_table h
     ) h left join
     happ_table hprev
     on hprev.idr = h.idr and
        hprev.date_from = h.prev_date_from;

Notes:

  • In Firebird 3+, this is much simpler with lag().
  • I'm not sure if the dates are unique. Perhaps the first column is the right column for specifying the "previous" row.

Upvotes: 1

Related Questions