SQL Server How to update Row by above empty row in groups

bxgwgixi  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(123)

I have a table

branch_no   crncy_id    Tax_1_Account   Tax_1_Balance       Tax_2_Account   Tax_2_Balance
1               3       01-01-211515    0.000000            NULL            NULL
1               3       01-01-230305    726910532.850000    NULL            NULL
1               3       NULL            NULL                01-01-240130    2019759.840000
1               3       01-01-240135    135117.090000       NULL            NULL
1               3       NULL            NULL                01-01-510170    2140859.810000
1               3       01-01-610501    0.000000            NULL            NULL
1               3       01-01-610520    37285.000000        01-01-610520    37285.000000
2               3       01-02-211515    0.000000            NULL            NULL
2               3       01-02-230305    14483229.720000     NULL            NULL
2               3       NULL            NULL                01-02-240130    85462.290000
2               3       01-02-240135    0.000000            NULL            NULL
2               3       NULL            NULL                01-02-510170    0.000000
2               3       01-02-610501    0.000000            NULL            NULL
2               3       01-02-610520    284409497.000000    01-02-610520    284409497.3             
3               3       01-03-211515    0.000000            01-03-211515    0.000000
3               3       01-03-230305    0.000000            01-03-230305    0.000000
3               3       NULL            NULL                01-03-240130    0.000000
3               3       01-03-240135    0.000000            01-03-240135    0.000000
3               3       NULL            NULL                01-03-510170    0.000000
3               3       01-03-610501    0.000000            01-03-610501    0.000000 
3               3       01-03-610520    710.000000          01-03-610520    710.000000

What I seek with is updating any NULLs that existing into Tax_1_Account , Tax_1_Balance , Tax_2_Account & Tax_2_Balance with the value that in next row in case same group of branch_no And Crncy_id and keep NULL at the last group for Tax_1_Account , Tax_1_Balance Or Tax_2_Account & Tax_2_Balance and delete record if there are NULLS into the 4 columns ( Tax_1_Account , Tax_1_Balance , Tax_2_Account & Tax_2_Balance )

Expected result:-

branch_no   crncy_id    Tax_1_Account   Tax_1_Balance       Tax_2_Account   Tax_2_Balance
1               3       01-01-211515    0.000000            01-01-240130    2019759.840000
1               3       01-01-230305    726910532.850000    01-01-510170    2140859.810000
1               3       01-01-240135    135117.090000       01-01-610520    37285.000000
1               3       01-01-610501    0.000000            NULL            NULL             
1               3       01-01-610520    37285.000000        NULL            NULL    
2               3       01-02-211515    0.000000            01-02-240130    85462.290000
2               3       01-02-230305    14483229.720000     01-02-510170    0.000000
2               3       01-02-240135    0.000000            01-02-610520    284409497.000000    
2               3       01-02-610501    0.000000            NULL            NULL
2               3       01-02-610520    284409497.000000    NULL            NULL
3               3       01-03-211515    0.000000            01-03-211515    0.000000
3               3       01-03-230305    0.000000            01-03-230305    0.000000
3               3       01-03-240135    0.000000            01-03-240130    0.000000
3               3       01-03-610501    0.000000            01-03-240135    0.000000
3               3       01-03-610520    710.000000          01-03-510170    0.000000
3               3       NULL            NULL                01-03-610501    0.000000 
3               3       NULL            NULL                01-03-610520    710.000000

Thanks.

sql-server

1条答案

按热度按时间
lp0sw83n

lp0sw83n1#

You can do something like this perhaps:

select *
, identity(int, 1, 1) as id
into #t
from 
(
    VALUES  (1, 3, N'01-01-211515', N'0.000000', null, null)
    ,   (1, 3, N'01-01-230305', N'726910532.850000', null, null)
    ,   (1, 3, null, null, N'01-01-240130', N'2019759.840000')
    ,   (1, 3, N'01-01-240135', N'135117.090000', null, null)
    ,   (1, 3, null, null, N'01-01-510170', N'2140859.810000')
    ,   (1, 3, N'01-01-610501', N'0.000000', null, null)
    ,   (1, 3, N'01-01-610520', N'37285.000000', N'01-01-610520', N'37285.000000')
    ,   (2, 3, N'01-02-211515', N'0.000000', null, null)
    ,   (2, 3, N'01-02-230305', N'14483229.720000', null, null)
    ,   (2, 3, null, null, N'01-02-240130', N'85462.290000')
    ,   (2, 3, N'01-02-240135', N'0.000000', null, null)
    ,   (2, 3, null, null, N'01-02-510170', N'0.000000')
    ,   (2, 3, N'01-02-610501', N'0.000000', null, null)
    ,   (2, 3, N'01-02-610520', N'284409497.000000', N'01-02-610520', N'284409497.3')
    ,   (3, 3, N'01-03-211515', N'0.000000', N'01-03-211515', N'0.000000')
    ,   (3, 3, N'01-03-230305', N'0.000000', N'01-03-230305', N'0.000000')
    ,   (3, 3, null, null, N'01-03-240130', N'0.000000')
    ,   (3, 3, N'01-03-240135', N'0.000000', N'01-03-240135', N'0.000000')
    ,   (3, 3, null, null, N'01-03-510170', N'0.000000')
    ,   (3, 3, N'01-03-610501', N'0.000000', N'01-03-610501', N'0.000000')
    ,   (3, 3, N'01-03-610520', N'710.000000', N'01-03-610520', N'710.000000')
) t (branch_no,crncy_id,Tax_1_Account,Tax_1_Balance,Tax_2_Account,Tax_2_Balance)

select branch_no, crncy_id
, MAX(case when type = 1 then value end), max(case when type = 1 then bal end)
, MAX(case when type = 2 then value end), max(case when type = 2 then bal end)
from (

    select  row_number() over(partition by branch_no, crncy_id, type order by case when x.value is null then 1 else 0 end, id) as sort
    ,   *
    from    #t
    cross apply (
        select Tax_1_Account as value, Tax_1_Balance as bal, 1 as type
        union all
        select Tax_2_Account as value, Tax_2_Balance as bal, 2
        ) x
    ) x
group by sort,branch_no, crncy_id
having max(x.value) is not null
order by branch_no, crncy_id, sort

Note, i create manual identity field (id) which ensures "correct" sort, but you probably have some other field like date which should be used to sort this table.

The rest of logic is following. I create two separate tables (type = 1 and type 2), that i sort by non-null values, and then i pivot them together to create the necessary final result.

having max(x.value) is not null removes the completely empty rows that occur after moving up

赞(0）分享  回复(0）举报  2023-10-15
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com