SQL Server查询以隐藏重复的行列数据。不想删除重复的行 [英] SQL Server query to hide duplicate rows column data. Don't want to remove a duplicate row

问题描述

SQL Server查询以隐藏重复的行列数据。不想删除重复的行。有条件地将数据显示为空白。

SQL Server query to hide duplicate rows column data. Don't want to remove a duplicate row. conditionally display data as blank.

何时，我运行以下SQL查询：

When, I run this SQL query:

select 
    [Vch No.], [Vch Type], [Vch Ref],
    [Date], [Party Name], [Sales Ledger],
    [Amt], [GST Ledger], [TaxAmount], [Total]
from 
    [AccountData]

我得到以下输出：

但是，我需要使用以下格式的输出：

But, I need output in this format:

在第二个打印屏幕中，我没有显示[Vch Ref]，[Date]，[Party Name]，[Sales Ledger]，[Amt]和Total值的显示。

In the second print screen, I don't what to display a value of [Vch Ref], [Date],[Party Name], [Sales Ledger], [Amt] and Total.

推荐答案

这似乎是一种疯狂的解决方案，但是您可以使用窗口函数 ROW_NUMBER（）并使用 CASE 表达式对其进行筛选，以检查行号是否大于1，例如：

This looks like crazy one solution, but you can achieve it using windowed function ROW_NUMBER() and using CASE expression check if row number is higher than 1, something like:

select 
    [Vch No.],
    [Vch Type],
    case when rn > 1 then '' else [Vch Ref] end as [Vch Ref],
    case when rn > 1 then '' else [Date] end as [Date],
    case when rn > 1 then '' else [Party Name] end as [Party Name],
    case when rn > 1 then '' else [Sales Ledger] end as [Sales Ledger],
    case when rn > 1 then '' else [Amt] end as [Amt],
    [GST Ledger],
    [TaxAmount],
    case when rn > 1 then '' else [Total] end as [Total]
from (  
    select 
        [Vch No.],
        [Vch Type],
        [Vch Ref],
        [Date],
        [Party Name],
        [Sales Ledger],
        [Amt],
        [GST Ledger],
        [TaxAmount],
        [Total], 
        row_number() over (partition by [Vch No.],[Vch Type],[Vch Ref],[Date],[Party Name],[Sales Ledger],[Amt],[GST Ledger],[TaxAmount],[Total] order by [Vch No.]) rn
    from [AccountData]
)x

查看数据类型，如果 Amt 为INT，则应将其转换为字符串想要获得空值。

Look at datatypes, if there Amt is INT you should convert it to string if you want to get blank value.

这篇关于SQL Server查询以隐藏重复的行列数据。不想删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！