Use SQL to export Parent/Child rows into a flat file

Question

With an Orders table (OrderID, date, customerID, status, etc) and an OrderDetails table (ParentID, itemID, quantity, price, etc), I would like to create a SQL Query that will export a CSV flat file with Order and OrderDetail rows interspersed. For instance, output might look like this ("H" and "D" indicate "Header" and "Detail" respectively.):

"H",2345,"6/1/09",856,"Shipped"
"D",2345,52,1,1.50
"D",2345,92,2,3.25
"D",2345,74,1,9.99
"H",2346,"6/1/09",474,"Shipped"
"D",2346,74,1,9.99
"D",2346,52,1,1.50

Not sure where to even start with this. Any ideas? TIA.

Answer 1

You'll want to take advantage of the fact that union all will honor the order by clause at the end on the entire result set. Therefore, if you order by the second column (2!) ascending, and the first column (1!) descending, you'll get the header row, then the detail rows underneath that.

Also, make sure that you have the same number of columns in the two queries. They don't have to be of the same data type, since you're exporting to CSV, but they do have to be the same number. Otherwise, the union all won't be able to pile them onto each other. Sometimes, you'll just have to pad columns with null if you need extra ones, or '' if you don't want the word null in your CSV.

select
    'H',
    OrderID,
    Date,
    CustomerID,
    Status
from
    Headers
union all
select
    'D',
    ParentID,
    ItemID,
    Quantity,
    Price
from
    Details
order by
    2 asc, 1 desc