將輸出分組和排序到單行

我想在一行中顯示每個人的聯繫資訊和他們的佣金，而不是多行。

佣金表

ID          COMMISSION  DATE                                        
N12545233    774         4/1/15                                     
N12545233    361         5/1/14                                     
N12545233    685         6/1/15                                     
N14521746   3384        10/1/14                                     
N14521746   1188        11/1/14                                     
N14521746   1256        12/1/14                                     
N15981070   1188         1/1/15                                     
N15981070    616         2/1/15                                     
N15981070    936         3/1/15 

銷售人員表

ID          LASTNAME    FIRSTNAME   STREET          CITY        STATE   ZIP                     
N12545233   Jones       Mark        123 Main St     Brooklyn    NY      11377                       
N14521746   Greene      Phil        345 Broadway    Queens      NY      11237                       
N15981070   Stahl       Barbara     567 Lexington   New York    NY      10018

期望的輸出

ID         LASTNAME FIRSTNAME   STREET        CITY      STATE   ZIP     COMMISSION  DATE      COMMISSION    DATE           COMMISSION   DATE
N12545233  Jones    Mark        123 Main St   Brooklyn  NY      11377   774          4/1/15    361           5/1/14          685         6/1/15
N14521746  Greene   Phil        345 Broadway  Queens    NY      11237   3384        10/1/14   1188          11/1/14         1256        12/1/14
N15981070  Stahl    Barbara     567 Lexington New York  NY      10018   1188         1/1/15    616           2/1/15          936         3/1/15

如果您在 MySQL 上執行：

SELECT
   c.ID,
   ss.LASTNAME,
   ss.FIRSTNAME,
   ss.STREET,
   ss.CITY,
   ss.STATE,
   ss.ZIP,
   GROUP_CONCAT(c.COMMISSION ORDER BY c.DATE SEPARATOR '\n') AS COMMISSION,
   GROUP_CONCAT(c.DATE ORDER BY c.DATE SEPARATOR '\n') AS DATE
FROM db1.Sales_Staff AS ss
JOIN db1.Commission AS c ON (c.ID=ss.ID)
ORDER BY ss.LASTNAME ASC,ss.FIRSTNAME ASC;

在甲骨文中

select a.*, b.commission, b.date
from sales_staff a, comission b
where a.id = b.id;

引用自：https://dba.stackexchange.com/questions/107281