tsql - How to convert multiples rows and columns into one row

Question

id | acct_num | name | orderdt              
1   1006A      Joe Doe  1/1/2021                
2   1006A      Joe Doe  1/5/2021    
        

EXPECTED OUTPUT

id | acct_num | name | orderdt | id1 | acct_num1 | NAME1 | orderdt1
1     1006A    Joe Doe  1/1/2021  2    1006A      Joe Doe   1/5/2021

My query is the following:

Select id,
     acct_num,
     name, 
    orderdt 
from order_tbl
where acct_num = '1006A' 
and orderdt >= '1/1/2021'

Answer 1

If you always have one or two rows you could do it like this (I'm assuming the latest version of SQL Server because you said TSQL):

NOTE: If you have a known max (eg 4) this solution can be converted to support any number by changing the modulus and adding more columns and another join.

 WITH order_table_numbered as
 (
    SELECT ID, ACCT_NUM, NAME, ORDERDT,
           ROW_NUMBER() AS (PARTITION BY ACCT_NUM ORDER BY ORDERDT) as RN
 )
 SELECT first.id as id, first.acct_num as acct_num, first.num as num, first.order_dt as orderdt,
        second.id as id1, second.acct_num as acct_num1, second.num as num1, second.order_dt as orderdt1
 FROM order_table_numbered first
 LEFT JOIN order_table_numbered second ON first.ACCT_NUM = second.ACCT_NUM and (second.RN % 2 = 0) 
 WHERE first.RN % 2 = 1

If you have an unknown number of rows I think you should solve this on the client OR convert the groups to XML -- the XML support in SQL Server is not bad.