PostgreSQL - 将单行更改为多行

Question

我有一个名为payment_info的表，其中包含以下记录。

paymentid | customercode | previousbalance | paymentamount | remainingbalance
-----------------------------------------------------------------------------
PID0001   |    CUST024   |    10000        |     2500      |   7500
PID0002   |    CUST031   |    8500         |     3500      |   5000
PID0003   |    CUST005   |    12000        |     1500      |   10500

我想要的是创建一个每行有3行的表格，我希望我的结果看起来像这样。

Payment Group | Payment Line Item | Payment ID | Customer Code |     Type            | Amount    
--------------------------------------------------------------------------------------------------
   1          |         1         |  PID0001   |   CUST024     | PREVIOUS BALANCE    | 10000.00    
   1          |         2         |            |               | PAYMENT AMOUNT      | 2500.00    
   1          |         3         |            |               | REMAINING BALANCE   | 7500.00    

   2          |         1         |  PID0002   |   CUST031     | PREVIOUS BALANCE    | 8500.00    
   2          |         2         |            |               | PAYMENT AMOUNT      | 3500.00    
   2          |         3         |            |               | REMAINING BALANCE   | 5000.00    

   3          |         1         |  PID0003   |   CUST005     | PREVIOUS BALANCE    | 12000.00    
   3          |         2         |            |               | PAYMENT AMOUNT      | 1500.00    
   3          |         3         |            |               | REMAINING BALANCE   | 10500.00    

这是我开始的查询语句，但它没有返回与上述结果相同的结果。

select row_number() over() as id,paymentid,customercode,'PREVIOUS BALANCE' as type,previousbalance from payment_info
union 
select row_number() over() as id,'','','PAYMENT AMOUNT' as type,paymentamount from payment_info
union 
select row_number() over() as id,'','','REMAINING BALANCE' as type,remainingbalance from payment_info

有没有其他方法可以不使用UNION关键字？因为在实际表中，我将使用30多个列，查询数千条记录。

我也不知道如何从付款组（每个付款ID）和付款行项目（每个组）创建自动生成的编号（ID）。

谢谢。

- user5934502

---

使用 UNION ALL 可以以更低的成本返回所有行，而不是仅使用 UNION。此外：不能保证 row_number() over() 会产生所需的排序 - 使用显式的 order by 来保证正确的排序。另请参见：https://dev59.com/cnNA5IYBdhLWcg3wAI3d - Paul Maxwell

我可以添加您的“支付组”号码 :) - flutter

根据要求添加了空格。 - flutter

空格文本版本现在更好了。 - flutter

Answer 1

带空格的版本（空文本） unnest 函数可以为您完成此操作。 如果您需要空文本，则可以使用此选项

SELECT ROW_NUMBER() OVER (ORDER BY paymentid) AS "group",  
unnest(array[1, 2, 3]) AS "line item",  
unnest(array[paymentid, '', '']) AS "paymentid",  
unnest(array[customercode, '', '']) AS "customercode",  
unnest(array['PREVIOUS BALANCE', 'PAYMENT AMOUNT', 'REMAINING BALANCE']) AS "type",  
unnest(array[previousbalance, paymentamount, remainingbalance]) AS "amount"  
FROM payment_info  
ORDER BY 1, 2 ;  

要获得这个

 group | line item | paymentid | customercode |       type        | amount 
-------+-----------+-----------+--------------+-------------------+--------
     1 |         1 | PID0001   | CUST024      | PREVIOUS BALANCE  |  10000
     1 |         2 |           |              | PAYMENT AMOUNT    |   2500
     1 |         3 |           |              | REMAINING BALANCE |   7500
     2 |         1 | PID0002   | CUST031      | PREVIOUS BALANCE  |   8500
     2 |         2 |           |              | PAYMENT AMOUNT    |   3500
     2 |         3 |           |              | REMAINING BALANCE |   5000
     3 |         1 | PID0003   | CUST005      | PREVIOUS BALANCE  |  12000
     3 |         2 |           |              | PAYMENT AMOUNT    |   1500
     3 |         3 |           |              | REMAINING BALANCE |  10500

如果您想在空白文本列中添加例如点或其他文本或箭头，可以使用unnest轻松实现。您可以单独控制这4个空文本值。

SELECT ROW_NUMBER() OVER (ORDER BY paymentid) AS "group",  
unnest(array[1, 2, 3]) AS "line item",  
unnest(array[paymentid, '      a', '      c']) AS "paymentid",  
unnest(array[customercode, '      b', '      d']) AS "customercode",  
unnest(array['PREVIOUS BALANCE', 'PAYMENT AMOUNT', 'REMAINING BALANCE']) AS "type",  
unnest(array[previousbalance, paymentamount, remainingbalance]) AS "amount"  
FROM payment_info   
ORDER BY 1, 2 ;  

生成

 group | line item | paymentid | customercode |       type        | amount 
-------+-----------+-----------+--------------+-------------------+--------
     1 |         1 | PID0001   | CUST024      | PREVIOUS BALANCE  |  10000
     1 |         2 |       a   |       b      | PAYMENT AMOUNT    |   2500
     1 |         3 |       c   |       d      | REMAINING BALANCE |   7500
     2 |         1 | PID0002   | CUST031      | PREVIOUS BALANCE  |   8500
     2 |         2 |       a   |       b      | PAYMENT AMOUNT    |   3500
     2 |         3 |       c   |       d      | REMAINING BALANCE |   5000
     3 |         1 | PID0003   | CUST005      | PREVIOUS BALANCE  |  12000
     3 |         2 |       a   |       b      | PAYMENT AMOUNT    |   1500
     3 |         3 |       c   |       d      | REMAINING BALANCE |  10500

这是一个非常灵活的解决方案，你知道的。

- flutter

---

谢谢回答...如果我想检索30多个列，使用这种查询（unnest）是否明智？ - user5934502

只需尝试一下，它应该非常高效。 - flutter

这帮助我生成了一份需要整个数据结构更改的报告……感谢您的帮助！ - emarshah

只想报告一下，虽然这种方法有点繁琐，但与通常的“UNION”方法相比，查询速度确实更快。在阅读这篇帖子之前，我已经考虑过使用“unnest”，但我想知道是否有一种无需使用数组的方法。看来是没有的 :) - undefined

Answer 2

并不总是需要使用联合查询。例如，在这里，您可以使用3行和交叉连接。这样做的好处是只需对源表进行一次遍历。

drop table if exists Table1;

CREATE TABLE Table1
    ("paymentid" varchar(7), "customercode" varchar(7)
     , "previousbalance" int, "paymentamount" int, "remainingbalance" int)
;

INSERT INTO Table1
    ("paymentid", "customercode", "previousbalance", "paymentamount", "remainingbalance")
VALUES
    ('PID0001', 'CUST024', 10000, 2500, 7500),
    ('PID0002', 'CUST031', 8500, 3500, 5000),
    ('PID0003', 'CUST005', 12000, 1500, 10500)
;

select
      paymentid
    , customercode
    , rn
    , typeof
    , case when rn = 1 then previousbalance
           when rn = 2 then paymentamount
           when rn = 3 then remainingbalance
      end as Amount
from Table1
cross join (select 1 rn , 'previousbalance' typeof
            union all 
            select 2 , 'paymentamount'
            union all 
            select 3, 'remainingbalance'
           ) rns

该数据/查询产生了以下结果：

+----+-----------+--------------+----+------------------+--------+
|    | paymentid | customercode | rn |      typeof      | amount |
+----+-----------+--------------+----+------------------+--------+
|  1 | PID0001   | CUST024      |  1 | previousbalance  |  10000 |
|  2 | PID0001   | CUST024      |  2 | paymentamount    |   2500 |
|  3 | PID0001   | CUST024      |  3 | remainingbalance |   7500 |
|  4 | PID0002   | CUST031      |  1 | previousbalance  |   8500 |
|  5 | PID0002   | CUST031      |  2 | paymentamount    |   3500 |
|  6 | PID0002   | CUST031      |  3 | remainingbalance |   5000 |
|  7 | PID0003   | CUST005      |  1 | previousbalance  |  12000 |
|  8 | PID0003   | CUST005      |  2 | paymentamount    |   1500 |
|  9 | PID0003   | CUST005      |  3 | remainingbalance |  10500 |
+----+-----------+--------------+----+------------------+--------+

请注意，SQL不是一个“报表编写器”，因此在“布局”中的列中留空不适合SQL，因为SQL希望重复信息（如上面所示的结果），以便您可以根据需要进行排序和过滤。

- Paul Maxwell

---

已经注意到了...感谢您的回答..但是我该如何在这里添加付款组列，每个付款ID自动编号？ - user5934502

虽然可以使用unnest()函数完成 :) Postgres Elephant可以做很多神奇的事情。 - flutter

仅仅因为某件事情可以做到，并不意味着它应该被做。 - Paul Maxwell