Repeat Row Values-CodePudding

I have the following data:

with source (Account,AccountNumber,Indentation) as
(
select 'INCOME STATEMENT',1000,0 union all
select 'REVENUE',1100,0 union all
select 'Revenue - Aircon',1110,1 union all
select 'Revenue - Consumer Goods',1120,1 union all
select 'Revenue - Spares',1130,1 union all
select 'Revenue - Accessories',1140,1 union all
select 'Revenue - Sub Stock',1150,1 union all
select 'Revenue - Services',1160,1 union all
select 'Revenue - Other',1170,1 union all
select 'Revenue - Intercompany',1180,1 union all
select 'Revenue - Delivery Charges',1400,1 union all
select 'COST OF SALES',1500,0 union all
select 'COGS - Aircon',1510,1 union all
select 'COGS - Consumer Goods',1520,1 union all
select 'COGS - Spares',1530,1 union all
select 'COGS - Accessories',1540,1 union all
select 'COGS - Sub Stock',1550,1 union all
select 'COGS - Services',1560,1 union all
select 'COGS - Other',1570,1 union all
select 'COGS - Intercompany',1580,1 union all
select 'COS - Sub Stock Stock Adjustments',1610,1 union all
select 'COS - Sub Stock Repairs',1620,1 union all
select 'COS - Consumables & Packing Materials',1810,1 union all
select 'COS - Freight & Delivery',1820,1 union all
select 'COS - Inventory Adj - Stock Count',1910,1 union all
select 'COS - Inv. Adj - Stock Write up / Write down',1920,1 union all
select 'COS - Provision for Obsolete Stock (IS)',1930,1 union all
select 'COS - Inventory Adj - System A/c',1996,1 union all
select 'COS - Purch & Dir. Cost Appl A/c - System A/c',1997,1 union all
select 'GROSS MARGIN',1999,0 union all
select 'OTHER INCOME',2000,0 union all
select 'Admin Fees Received',2100,1 union all
select 'Bad Debt Recovered',2110,1 union all
select 'Discount Received',2120,1 union all
select 'Dividends Received',2130,1 union all
select 'Fixed Assets - NBV on Disposal',2140,1 union all
select 'Fixed Assets - Proceeds on Disposal',2145,1 union all
select 'Rebates Received',2150,1 union all
select 'Rental Income',2160,1 union all
select 'Sundry Income',2170,1 union all
select 'Warranty Income',2180,1 union all
select 'INTEREST RECEIVED',2200,0 union all
select 'Interest Received - Banks',2210,1
)

select
    Account
,   AccountNumber
,   Indentation
from    source;

Using the following script:

with s as (
select
    iif(Account like 'Total%',null,iif(Indentation=0,Account,null)) Header
,   iif(Account like 'Total%',null,iif(Indentation=1,Account,null)) SubHeader1
,   *
from    Source
)

select
    Header
--, case lag(Header) over (order by [Account Number]) when Header then isnull(Header,lag(Header) over (order by [Account Number])) else Header end
,   SubHeader1
,   [Account Number]
,   Indentation
from    s

I'm able to split the columns like this:

I need to be able to report the Header Column to look like this:

I tried doing it using LAG(), but it doesn't work, how would I script this?

CodePudding user response：

This is one option. I created a Group for each header and then used it to grab the first in that group that had an Indention = 0. Tack this on to your source CTE:

,CTE2 AS
(
select
    iif(Account like 'Total%',null,iif(Indentation=0,Account,null)) Header
,   iif(Account like 'Total%',null,iif(Indentation=1,Account,null)) SubHeader1
, SUM(CASE WHEN Indentation = 0 THEN 1 ELSE 0 END) OVER (ORDER BY AccountNUmber) H1
,   *
from    Source
)

SELECT T2.Header, t1.SubHeader1, t1.AccountNumber, t1.Indentation
FROM CTE2 t1
CROSS APPLY(SELECT MAX(t3.HEADER) HEADER FROM CTE2 T3 where t3.H1 = T1.H1 and T3.Indentation = 0 ) T2
ORDER BY t1.AccountNumber