I have a table containing several kilometers about 100 table of kilometers

I need to rotate rows into columns like this pivot rows into columns

1. The number of columns is fixed in rows = 10
2. Kilometers can be repeated and have gaps, for exemple: 263, 263, 264, 265, 270..

I think to use "with recursive", but there are few examples on the net ( I would be very grateful if you could help me solve the problem!

CodePudding user response：

As I mentioned in the comments, this is probably something better solved in your presentation layer, instead of through a query. I'm not sure if this can be solved with a (recursive) CTE, but I can offer you a solution in PSQL using EXECUTE BLOCK (this can also be done in the form of a stored procedure).

This solution maps rows to columns, and starts a new row when all columns have been filled.

execute block
returns (km_1 smallint, km_2 smallint, km_3 smallint, km_4 smallint, km_5 smallint,
  km_6 smallint, km_7 smallint, km_8 smallint, km_9 smallint, km_10 smallint)
as
  declare column_count smallint = 1;
  declare km smallint;
begin
  for select km from kms order by km into km do
  begin
    -- map value to column
    if (column_count = 1) then km_1 = km;
    else if (column_count = 2) then km_2 = km;
    else if (column_count = 3) then km_3 = km;
    else if (column_count = 4) then km_4 = km;
    else if (column_count = 5) then km_5 = km;
    else if (column_count = 6) then km_6 = km;
    else if (column_count = 7) then km_7 = km;
    else if (column_count = 8) then km_8 = km;
    else if (column_count = 9) then km_9 = km;
    else if (column_count = 10) then km_10 = km;
    
    if (column_count < 10) then
      column_count = column_count   1;
    else
    begin
      -- row complete, output
      column_count = 1;
      suspend;
    end
  end
  if (column_count > 1) then
  begin
    -- partial row
    while (column_count <= 10) do
    begin
      -- null remaining columns
      if (column_count = 2) then km_2 = null;
      else if (column_count = 3) then km_3 = null;
      else if (column_count = 4) then km_4 = null;
      else if (column_count = 5) then km_5 = null;
      else if (column_count = 6) then km_6 = null;
      else if (column_count = 7) then km_7 = null;
      else if (column_count = 8) then km_8 = null;
      else if (column_count = 9) then km_9 = null;
      else if (column_count = 10) then km_10 = null;
      column_count = column_count   1;
    end
    -- output partial row
    suspend;
  end
end

In Firebird 4.0 you can achieve the same result with window functions.

with col_row as (
  select 
    km, 
    mod(row_number() over(order by km) - 1, 10) as column_group, 
    (row_number() over(order by km) - 1) / 10 row_group
  from kms
)
select 
  min(km) filter (where column_group = 0),
  min(km) filter (where column_group = 1),
  min(km) filter (where column_group = 2),
  min(km) filter (where column_group = 3),
  min(km) filter (where column_group = 4),
  min(km) filter (where column_group = 5),
  min(km) filter (where column_group = 7),
  min(km) filter (where column_group = 8),
  min(km) filter (where column_group = 9)
from col_row
group by row_group

For Firebird 3.0, replace min(km) filter (where column_group = n) with min(decode(column_group, n, km)) or min(case when column_group = n then km) (where n is 0, 1, .., 9).