重復行值

我有以下資料：

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;

使用以下腳本：

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

我可以像這樣拆分列：

我需要能夠報告標題列如下所示：

我嘗試使用 LAG() 來做，但它不起作用，我該如何撰寫腳本？

uj5u.com熱心網友回復：

這是一種選擇。我為每個標題創建了一個組，然后用它來獲取該組中縮進 = 0 的第一個。將其粘貼到您的源 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

標籤：查询语句 sql-server-2019

上一篇：隨著時間的推移選擇每個不同的值，而不會丟失其間的NULL

下一篇：SQLServerOptimizer洗掉ISNULL呼叫