有没有办法在没有特定列数的情况下动态地将行旋转到列 [英] is there any way to pivot rows to columns dynamically without a specific no of columns in firebird

问题描述

我需要一种使用 sql 动态显示从行到列的数据的方法.我的行号从 0 到 N 开始，我想将其转换为列.我不能使用静态列来满足我的要求，因为每次的最大行数都会根据公司要求的政策而变化.我已经完成了研究，但 firebird 没有枢轴/转置/交叉表实现(我可能错了).这是我的桌子

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

这是我的客户表

这是我的应付表

因为涉及的客户很多，所以需要这样显示

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

您可以注意到我的客户可以有 0 到 N 的应付金额.

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

有没有办法使用firebird sql来实现它?

Is there a way to implement it using firebird sql?

推荐答案

我们在使用 Firebird 的环境中遇到过这种情况.马克是正确的，你不能做动态枢轴，但在我们的场景中需要那个功能.我们实现的是让我们的前端调用 Firebird 中的一个存储过程，该存储过程将构建"固定枢轴的 SQL，然后返回 SQL，然后前端将执行 SQL.对用户来说，它看起来像一个动态 SQL.

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.

在您的特定情况下，常规 sql 就足够了.

In your specific case regular sql should suffice.

如果您从前端执行此操作，则会返回一条 SQL 语句.

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

我不得不删除列标签中的空格，因为 Firebird 不喜欢标签中的空格.但是，如果您随后执行 SQL，它应该可以按您的意愿工作.这将针对每种不同的贷款类型动态扩展.

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.

这篇关于有没有办法在没有特定列数的情况下动态地将行旋转到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！