How to Show Blank Record in SQL If Duplicate Rows Exists

How to show blank record in sql if duplicate rows exists

You can do this:

;WITH CTE
AS
( 
   SELECT EmployeeID, EmployeeName, Department,
     ROW_NUMBER() OVER(PARTITION BY Department ORDER BY(SELECT 1)) rownum
  FROM table1
)
SELECT
  EmployeeId,
  EmployeeName,
  CASE rownum
    WHEN 1 THEN Department
    ELSE ''
  END AS Department
FROM CTE
ORDER BY EmployeeID;

SQL Fiddle Demo

Note that: Try not to do these sort of formatting stuff in SQL. SQL is not about formatting.

Duplicate data in unique rows, show blank instead

You can do what you want in the database. However, such presentation considerations are often better done at the application layer. Here is one method:

with bc as (
      SELECT b.title AS Title, c.[Name] AS Char_Name, b.Plot_Summary AS Summary
      FROM Books b INNER JOIN
           Book_Char bc
           ON b.id = bc.Book_id INNER JOIN
           Characters c
           ON c.id = bc.Char_id
     )
select (case when seqnum = 1 then bc.title else '' end) as title, bc.Char_name,
       (case when seqnum = 1 then bc.Summary else '' end) as Summary
from (select bc.*, row_number() over (partition by title order by char_name) as seqnum
      from bc
     ) bc
order by bc.title, bc.char_name;

The real problem with your query, though, is that you are expecting results in a particular order. You only get results in a particular order (guaranteed) when you use order by. And, your query has no order by.

shows blanks for repeating values in a result set

Wrap your existing query in CTE adding ROW_NUMBER OVER PARTITION BY your columns, which will create RNs for each group of values. In outer query just use CASE to select values where GRP_RN = 1 and empty string otherwise.

WITH CTE AS 
(
    Select  distinct top 100000 
        o.EventSetName,       
        o.EventSetDisplay,
        o.EventSetDescription,
        o.ChildSetName,
        ROW_NUMBER() Over (Order By f.ChildSetName) RN,
        f.DocumentDispSequence,
        f.SectionDispSequence,
        o.ObsSetDispSequence,
        null  as NullColumnNeedsName,
        ROW_NUMBER() OVER (PARTITION BY o.EventSetName, o.EventSetDisplay,o.EventSetDescription ORDER BY f.ChildSetName) GRP_RN
    From   ##ObsSetLevel o,
    INNER JOIN ##Final f ON f.ChildSetName = o.EventSetName and o.EventSetName = @variableName
)
SELECT
    CASE WHEN GRP_RN = 1 THEN o.EventSetName ELSE '' END AS EventSetName,
    CASE WHEN GRP_RN = 1 THEN o.EventSetDisplay ELSE '' END AS EventSetDisplay,
    CASE WHEN GRP_RN = 1 THEN o.EventSetDescription ELSE '' END AS EventSetDescription,
    other columns
FROM CTE  
Order By RN asc, DocumentDispSequence asc, SectionDispSequence asc, o.ObsSetDispSequence asc

PS: I have also corrected your use of old-style joins. That usage is outdated more than 20 years ago with introduction of SQL-92 standards. You should avoid using them.

Eliminating duplicate rows with null values using with clause

This is a prioritization query. One method is to use row_number(). If you want only one value per Com_no/Company/Loc, then:

select x.*
from (select x.*,
             row_number() over (partition by Com_no, Company, Loc order by Rewards nulls last) as seqnum
      from x
     ) x
where seqnum = 1;

Or even:

select Com_no, Company, Loc, max(Rewards)
from x
group by Com_no, Company, Loc;

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

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

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

How to replace duplicate cells with blanks/nulls in SQL server 2008?

You can do this using left join and row_number window function.
Please check the columns for correctness before running this query.

SELECT   T2.[Operation cancelled within 28 days]
        ,T2.[Number of new patients seen]
        ,T2.[Number of follow up patients seen]
        ,T2.[Readmission Rate]
        ,T2.[2 Week Target]
        ,T1.[Procedure Split] 
FROM
    (SELECT DISTINCT [Operation cancelled within 28 days]
                     ,[Procedure Split] 
                     ,ROW_NUMBER() OVER(ORDER BY NUM) AS RN FROM Your_table) T1

 LEFT JOIN 

    (SELECT * FROM (SELECT DISTINCT  [Operation cancelled within 28 days]
                                    ,[Number of new patients seen]
                                    ,[Number of follow up patients seen]
                                    ,[Readmission Rate]
                                    ,[2 Week Target]
                                    ,ROW_NUMBER() OVER(ORDER BY A) AS RN FROM Your_table) X 
    WHERE RN=1 ) T2
ON T1.RN = T2.RN

SQL select distinct rows and ignore row if blank

Simple solution:

SELECT DISTINCT meta_value 
FROM `wp_postmeta` 
WHERE meta_key = "aaa" AND meta_value != "";

How to show blank instead of column value for all duplicated columns of a SQL query?

If you're in SQL Server 2012 or newer, you can do this with lag, something like this:

select
  nullif(column1, lag(column1) over (order by yourorderbyclause)) as column1,
  nullif(column2, lag(column2) over (order by yourorderbyclause)) as column2,
  ...
from
  View1

To make it dynamic, well then you have to parse a lot of metadata from the query. Using sp_describe_first_result_set might be a good idea, or use select into a temp. table and parse the columns of it.

Efficient way to remove rows with empty column if more than one row already exists

Something like this?

select t.*
from t
where t.col3 is not null
union all
select t
from t
where t.col3 is null and
      not exists (select 1 from t t2 where t2.col2 = t.col2 and t2.col3 is not null);

That is, select all non-null values. Then select null values where there is no other value.

If you are deleting rows, then:

delete from t
    where t.col3 is null and
          exists (select 1 from t t2 where t2.col2 = t.col2 and t2.col3 is not null);

---

Related Topics