is there any way to pivot rows to columns dynamically without a specific no of columns in firebird

Question

I need a way to display my data from rows to columns dynamically using sql. My row no. starts from 0 to N and I want to convert it to columns. I cannot use static column to match my requirement because the maximum no of rows changes every time depending on the policy required by the company. I have done researching but firebird has no pivot/transpose/cross-tab implementation (i might be wrong). Here are my tables

here is my client tables

here is my payable table

i need to display like this since there are many clients involve

as you can notice my client can have 0 to N payable.

Is there a way to implement it using firebird sql?

"*without a specific no of columns*" - no that's not possible. The number of columns of a query has to be known to the database engine **before** the query is actually run, it can not be determined "while it's running". (it's a restriction of the SQL query language and not specific to Firebird, it's true for all relational databases) — , Apr 01 '19 at 06:50
so you're saying that its not possible that you can set the no of columns using the no of rows that can be initially queried? cause what I'm thinking is getting the rows then instantiating it as a column. i just don't have the knowledge to implement it using procedures? — Mandz, Apr 01 '19 at 07:27
You need some kind of dynamic SQL where you first determine how many columns you need, then build a SQL query with that number of columns (and the correct SQL expressions obviously), then run that generated query. In most cases, it's much better to do that kind of transformation in the front end (after retrieving the data as rows). SQL is simply not designed to do that. — , Apr 01 '19 at 07:29

score 1 · Accepted Answer · edited Feb 26 '21 at 13:03

1

We have encountered this situation in our environment with Firebird. Mark is correct you can't do dynamic pivot, but in our scenario needed that functionality. What we implemented was for our frontend to make a call to a stored procedure in Firebird which would "build" the SQL for the fixed pivot and then return the SQL and then the frontend would then execute the SQL. To the user it would look like a dynamic SQL.

In your specific case regular sql should suffice.

if you execute this from the front end this will return you a SQL statement.

with cte as (
Select DISTINCT loantype,
       'SUM(CASE loantype WHEN ''' || loantype || ''' then loanamt' || ' ELSE 0 END) ' CASE_STMT  from tblpayables
             )
    Select 'Select m.MEMBERID ,'
           || cast( List( cte.case_stmt  || replace(loantype,' ','')) as varchar(3000))
           ||' from tblmembers  m inner join tblpayables p on m.MEMBERID = p.MEMBERID group by m.MEMBERID'
    from cte

the query above will return this result (I formatted so it's more readable).

Select m.MEMBERID ,
       SUM(CASE loantype WHEN 'loan type 1' then loanamt ELSE 0 END) loantype1,
       SUM(CASE loantype WHEN 'loan type 2' then loanamt ELSE 0 END) loantype2,
       SUM(CASE loantype WHEN 'loan type 3' then loanamt ELSE 0 END) loantype3,
       SUM(CASE loantype WHEN 'loan type 4' then loanamt ELSE 0 END) loantype4
from tblmembers  m
inner join tblpayables p on m.MEMBERID = p.MEMBERID
group by m.MEMBERID

I had to remove the spaces within the column labels because Firebird didn't like spaces in the labels. But if you then execute the SQL it should work as you want. This will dynamically expand for each distinct loan types.

edited Feb 26 '21 at 13:03

Mark Rotteveel

100,966
191
140
197

answered Apr 02 '19 at 12:37

Ed Mendez

1,510
10
14

why could not front-end itself generate the SQL ? – Arioch 'The Apr 02 '19 at 16:39
1

@Arioch'The The question stated if there was a way to implement this in firebird sql. But If the logic to derive the dynamic SQL is minimal then sure it can be done on the frontend. But in my personal experience, the frontend developers aren't intimately familiar in the Database's domain logic and when we have needed the do the dynamic pivots, it wasn't just one table transposed, it involved familiarity with the database domain to traverse the different tables involved. – Ed Mendez Apr 02 '19 at 17:29
@Arioch'The Ed Mendez is correct. with our current distribution of work my colleague (front end developer) does not even understand the logic of our system implementation on database side. That's why I'm trying to accomplish all of the computation on database sql side so that the only thing that he will do is just to display those data. – Mandz Apr 04 '19 at 01:57
@EdMendez maybe you're right cause i've been searching like crazy. Trying any procedure i can think of with no luck. Maybe ill just implement the pivoting via the front end. I'll just wait for some more time before choosing an answer so that we can have more idea on how to solve such problem. thx – Mandz Apr 04 '19 at 02:04
make 3-tier system, aka AppServer ? But of course just for one single query it seems overkill – Arioch 'The Apr 04 '19 at 08:02
@Mandz I updated my answer showing how to execute SQL to get back a SQL Select that will return a dynamic pivot. I hope this works. – Ed Mendez Apr 04 '19 at 14:34
You can use spaces in column labels if you quote the label (eg `"loan type 1"`) – Mark Rotteveel Apr 12 '19 at 09:23
@MarkRotteveel I was testing/running this query within IBEXPERT and it complained about the quotes, but if you submit this via a frontend the quotes should work. I should have clarified that. – Ed Mendez Apr 12 '19 at 11:25
@EdMendez That would suggest you were connected in IBExpert using dialect 1 (which doesn't support quoted identifiers). – Mark Rotteveel Apr 12 '19 at 11:32
@MarkRotteveel Ahhh Yes! Our organization is still using dialect 1. – Ed Mendez Apr 12 '19 at 12:03

score 0 · Answer 2 · answered Apr 01 '19 at 07:37

0

You can't create a dynamic pivot using SQL in Firebird. You can achieve a fixed pivot with CASE WHEN, or - in Firebird 4 - with the FILTER clause, but dynamic pivots are not possible.

You will need to dynamically generate the necessary query, or query the data and transform it in your front-end.

answered Apr 01 '19 at 07:37

Mark Rotteveel

100,966
191
140
197

ok noted. At least I can start thinking on how will I implement it using my front end. I just thought that it might be possible using procedures. – Mandz Apr 01 '19 at 07:53
@Mandz Procedures have a fixed number of output columns, with fixed names, so stored procedures can't do it either. – Mark Rotteveel Apr 01 '19 at 07:55
ok at least i will just have to tackle this problem in a different manner. i will just have to set the maximum column using assumptions. – Mandz Apr 01 '19 at 08:08

is there any way to pivot rows to columns dynamically without a specific no of columns in firebird

2 Answers2

Linked