I have an InvoiceLineItem table where line number is not added to the table. I have to create it on the fly while processing the table.

The line items can be grouped by

1. Invoice Id and
2. Row that end with a pattern PO[0-9]

This is my table

I am expecting the results to be

I do not have any idea how to achieve this.

I tried using the rownumber function but it does not give the expected results.

`SELECT
Invoice,
PurchaseOrderNumber,
Quantity,
UnitPrice,
LineItemTotal,
ROW_NUMBER() OVER (PARTITION BY Invoice ORDER BY (SELECT NULL)) AS RN
FROM
InvoiceLineItems
WHERE
PurchaseOrderNumber NOT LIKE ‘PO%’ and Template = ‘cw_engineering’

Union

SELECT
Invoice,
PurchaseOrderNumber,
NULL AS Quantity,
NULL AS UnitPrice,
NULL AS LineItemTotal,
ROW_NUMBER() OVER (PARTITION BY Invoice ORDER BY (SELECT NULL)) AS RN
FROM
InvoiceLineItems
WHERE
PurchaseOrderNumber LIKE ‘PO%’ and Template = ‘cw_engineering’
ORDER BY
Invoice, RN, PurchaseOrderNumber;`

It works if I have 2 records for each line number.