CODEWITHSUNDEEP
firebirdtransposeunionsfirebird2.5
Artemios Antonio Balbach
Artemios Antonio Balbach

Reputation: 425

Transpose columns and rows in Firebird 2.5

I've written a procedure in Firebird (Dialect 3), which returns me something like this:

 column1  |  column2    |  column3  |   column4  |  ...
----------|-------------|-----------|------------|--------
 1        |  55         |  2.5      |    100€    |  ...

The specific column names don't really matter. I access it like this

SELECT * FROM MY_PROCEDURE(:START_DATE, :END_DATE);

It only return one row so I guess I could also access it with EXECUTE_PROCEDURE. Now what I want is to transpose the columns and the rows in the return

 row      |  result    
----------|---------
 column1  |  1    
 column2  |  55    
 column3  |  2.0   
 column4  |  100€    
 ...      |  ...

What I initially did is somethink like this:

select 'column1' AS row, column1 AS result
FROM MY_PROCEDURE(:START_DATE, :END_DATE)
union all
select 'column2' AS row, column2 AS result
FROM MY_PROCEDURE(:START_DATE, :END_DATE)
union all
...

Basically one query for each column. It worked. However, eventually I ran into this problem:

Dynamic SQL Error
Too many Contexts of Relation/Procedure/Views. Maxium allowed is 255.

So I need to restructure my script. As you can see, my SQL knowledge is pretty mediocre, and I simply don't know how to fetch each column as a row in a single select.

Would anyone be able to help? Thanks in advance.

Upvotes: 3

Views: 2518

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109014

Firebird by itself as no unpivot or other built-in support for transposing columns.

The 'best' solution, and probably the most performing solution would be to rewrite MY_PROCEDURE (or write an alternative version) to output the rows transposed.

For example, assuming your stored procedure does something like this:

set term #;
create procedure test_1 
    returns (id integer, column1 double precision, column2 double precision, column3 double precision)
as
begin 
    for 
        select id, column1, column2, column3 
        from sometable 
        into :id, :column1, :column2, :column3 do
    begin
        suspend;
    end
end#
set term ;#

You can then rewrite this by manually transposing the values into separate suspends:

set term #;
create procedure test_2 
    returns (id integer, columnname varchar(100), columnvalue double precision)
as
declare column1 double precision;
declare column2 double precision;
declare column3 double precision;
begin 
    for 
        select id, column1, column2, column3 
        from sometable 
        into :id, :column1, :column2, :column3 do
    begin
        columnname = 'column1';
        columnvalue = column1;
        suspend;
        columnname = 'column2';
        columnvalue = column2;
        suspend;
        columnname = 'column3';
        columnvalue = column3;
        suspend;
    end
end#
set term ;#

This will output something like

id  columnname columnvalue
1   column1    1.0
1   column2    1.5
1   column3    5.0
2   ...etc

This solution does require that all output (columnvalue) has the same type. Otherwise you will need to cast to a common data type.

Alternatively, you could chain the first procedure into the second procedure by using for select * from test_1 into .... This maybe more or less efficient depending on the internals of your stored procedure:

set term #;
create procedure test_3 
        returns (id integer, columnname varchar(100), columnvalue double precision)
as
declare column1 double precision;
declare column2 double precision;
declare column3 double precision;
begin 
    for 
        select id, column1, column2, column3 from test_1
        into :id, :column1, :column2, :column3 do
    begin
        columnname = 'column1';
        columnvalue = column1;
        suspend;
        columnname = 'column2';
        columnvalue = column2;
        suspend;
        columnname = 'column3';
        columnvalue = column3;
        suspend;
    end
end#
set term ;#

This last option is probably best if you need both variants of the output, as this means you will only have single place for the logic of that stored procedure.

For ad-hoc querying, you can also replace the stored procedure with an execute block with the same code.

Upvotes: 2

Related Questions