Group and order output into a single row I want to display each person's contact info and their commissions in a single row as opposed to multiple rows.



Commission Table

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 


Sales_Staff Table

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


Desired Output

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
If you're running on 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;