What would be the best way of writing out Header Line and Sub line to a csv file

Question

Hi,

I want to be able to write out a header line then a sub line then a header line sub etc

To a CSV file for example

CREATE TABLE Sales_Header(
    sales_order_num int PRIMARY KEY,
    sales_date datetime ,
    customer_name nvchar(50),
);

CREATE TABLE Sales_item(
    sales_order_num int PRIMARY KEY,
    product_code nvchar(20),
    product_name nvchar(20),
    product_price int,
);

insert into Sales_Header
values (2039489, 25-01-2021, Mrs Smith),
            (2039460, 25-01-2021, Mr Smith);

insert into Sales_item
values (2039489, SKU987, Kitchen Utensils, £3.00),
           (2039489, SKU934, Knife, £5.00),
           (2039460, SKU47, Calendar, £6.00),
           (2039460, SKU87, Stationary, £2.00);

Desire output is

2039489, 25-01-2021, Mrs Smith
2039489, SKU987, Kitchen Utensils, £3.00
2039489, SKU934, Knife, £5.00
2039460, 25-01-2021, Mr Smith
2039460, SKU47, Calendar, £6.00
2039460, SKU87, Stationary, £2.00

What would be the best way of going about this?

Thanks

SQL server version 2016

Answer 1

If we can agree that the data source contains one row for each item sold(?), and each row contains all the data, then something like this will produce the desired result:

$dataSource = Import-CSV C:\Junk\DPD.csv

$OutFile = 'C:\Junk\DPD.txt'
$key = ""
$OneShot = $true
For ($i=0; $i -lt $dataSource.Length; $i++){
    If($OneShot){
        $key = $dataSource[$i].Order
        $OneShot = $false
        $dataSource[$i].Order,$dataSource[$i].Date,$dataSource[$i].Name -join "," | Out-File $OutFile
    }
    if ($key -ne $dataSource[$i].Order){
        $key = $dataSource[$i].Order
        $dataSource[$i].Order,$dataSource[$i].Date,$dataSource[$i].Name -join "," | Add-Content $OutFile
    }
    $dataSource[$i].Order,$dataSource[$i].SKU,$dataSource[$i].Desc,$dataSource[$i].Price -join "," | Add-Content $OutFile
}

The input to that code was this file:

Order,Date,Name,SKU,Desc,Price
2039489,25-01-2021,Mrs Smith,SKU987,Kitchen Utensils,£3.00
2039489,25-01-2021,Mrs Smith,SKU934,Knife,£5.00
2039460,25-01-2021,Mr Smith,SKU47,Calendar,£6.00
2039460,25-01-2021,Mr Smith,SKU87,Stationary,£2.00

And the output was:

2039489,25-01-2021,Mrs Smith
2039489,SKU987,Kitchen Utensils,£3.00
2039489,SKU934,Knife,£5.00
2039460,25-01-2021,Mr Smith
2039460,SKU47,Calendar,£6.00
2039460,SKU87,Stationary,£2.00

Answer 2

The file would have to be created by a client program which runs two queries, one for the headers and one for the details. You loop over the headers and print them, and then you lookup the details on the order id or whatever that number is.

As for what to write the program, just pick the language you are most comfortable with.