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
- The number of columns is fixed in rows = 10
- 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!
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.
In Firebird 4.0 you can achieve the same result with window functions.
For Firebird 3.0, replace
min(km) filter (where column_group = n)
withmin(decode(column_group, n, km))
ormin(case when column_group = n then km)
(where n is 0, 1, .., 9).