FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

;with base_table  as (
select
'AP' as [Type],	'758375000000' [Reference]	,'D000000016' [VAT Code]	,'128' as Amount	,'758375-DLSF Payment' [Description]	,'WCC'  as [Cost Centre] union all select
'GL',	'758375000000'	,'V0',	'128'	,'758375-DLSF Payment'	,'W22003' union all select
'AP',	'563753000000'	,'D000000016',	'20'	,'563753-DLSF Payment'	,'WCC' union all select
'GL',	'563753000000'	,'V0',	'20'	,'563753-DLSF Payment'	,'W22003' union all select
'AP',	'421765000000'	,'D000000016',	'56'	,'421765-DLSF Payment'	,'WCC' union all select
'GL',	'421765000000'	,'V0',	'56'	,'421765-DLSF Payment'	,'W22003'

)
, NumberedRows AS (
select base_table.* 
, ROW_NUMBER() OVER (Partition by Reference ORDER BY try_cast([Reference] as bigint)) AS RowNum
from base_table
)
 
, mytable as (
SELECT Type
, Reference
, [VAT Code]
, Amount
, Description
,[Cost Centre]
,RowNum
FROM NumberedRows
)

, row_data AS (
    SELECT 
        Type, 
        Reference, 
        [VAT Code], 
        Amount, 
        Description, 
        [Cost Centre], 
        RowNum, 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowPosition
    FROM mytable
)
,union_data as (
SELECT 
    Type, 
    Reference, 
    [VAT Code], 
    Amount, 
    Description, 
    [Cost Centre], 
    RowNum,
	RowPosition
FROM row_data

UNION ALL

-- Insert blank rows after RowNum = 2
SELECT 
    NULL AS Type, 
    NULL AS Reference, 
    NULL AS [VAT Code], 
    NULL AS Amount, 
    NULL AS Description, 
    NULL AS [Cost Centre], 
     RowNum,
	RowPosition
FROM row_data
WHERE RowNum = 2
)
,dummy_header as (
Select

Type, 
      Reference, 
     [VAT Code], 
      Amount, 
      Description, 
      [Cost Centre]
	  ,1 as ordering
	  ,RowNum
	  ,RowPosition

from union_data

union all
select 
'Type' as [Type], 
      'Reference' as Reference , 
     '[VAT Code]' as [VAT Code], 
     'Amount' as  Amount, 
      'Description' as Description, 
     '[Cost Centre]' as [Cost Centre]
	  ,-1 as ordering
	  ,-1 as rownum
	  ,-1 as RowPosition

)

Select 
Type, 
      Reference, 
     [VAT Code], 
      Amount, 
      Description, 
      [Cost Centre]

from dummy_header
ORDER BY  RowPosition,RowNum;

Type	Reference	VAT Code	Amount	Description	Cost Centre
Type	Reference	[VAT Code]	Amount	Description	[Cost Centre]
AP	421765000000	D000000016	56	421765-DLSF Payment	WCC
GL	421765000000	V0	56	421765-DLSF Payment	W22003
NULL	NULL	NULL	NULL	NULL	NULL
GL	563753000000	V0	20	563753-DLSF Payment	W22003
AP	563753000000	D000000016	20	563753-DLSF Payment	WCC
NULL	NULL	NULL	NULL	NULL	NULL
GL	758375000000	V0	128	758375-DLSF Payment	W22003
AP	758375000000	D000000016	128	758375-DLSF Payment	WCC
NULL	NULL	NULL	NULL	NULL	NULL

(10 rows affected)