Home 2017 November SQL Puzzle: SQL Advance Query – Reduce duplicate column data by merging ID with it

SQL Puzzle: SQL Advance Query – Reduce duplicate column data by merging ID with it

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output and concat id column with the duplicate name column and avoid reduce duplicate strings.

Input Data:

Expected Output:

Create a sample table with data:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Server: How to Remove Forwarded Records of a Heap Table?SQL Server 2016: New COMPRESS and DECOMPRESS function for the Column level Compression
Comments: 5
  1. Dinesh.IS
    November 7, 2017 at 7:25 am

    ;
    WITH CTE
    As
    (Select Row_Number()Over(Partition By Name Order By Name) As SLNo,* From tbl_DuplicateString)
    Select ID,(Case When NoOfCnt>1 Then A.Name+Convert(VarChar(2),SLNo) Else A.Name End) As Name From CTE A
    Inner Join (Select Name,Count(*) As NoOfCnt
    From CTE
    Group By Name) B On B.Name=A.Name

    ReplyCancel
  2. Pree
    August 25, 2018 at 1:26 pm

    select id
    , case when n1.count >1 then t.name || t.ID
    else t.name end as Name
    from tbl_DuplicateString t
    left join (select name
    , count(*)
    from tbl_DuplicateString
    group by name) n1 on t.name = n1.name

    ReplyCancel
  3. Marcel Hofman
    March 26, 2019 at 10:17 am

    with cte as
    (
    select *, ROW_NUMBER () OVER (partition by name ORDER BY ID) AS duplicates from tbl_DuplicateString
    )
    select case when duplicates = 1 then name
    ELSE name + CAST(duplicates AS VARCHAR(10))
    END AS name FROM cte

    ReplyCancel
  4. Satish Kumar
    April 6, 2020 at 8:39 am

    SELECT
    T1.ID,COALESCE(X.NAME+CAST(X.RN AS VARCHAR(10)),T1.NAME) AS [NAME]
    FROM
    #tbl_DuplicateString T1
    LEFT JOIN
    (
    SELECT
    *
    FROM
    (
    select
    ID,NAME,
    ROW_NUMBER()over(partition by name order by id) AS RN,
    COUNT(1) OVER(PARTITION BY NAME) CNT
    from
    #tbl_DuplicateString
    GROUP BY ID,NAME
    ) Y
    WHERE
    CNT >1

    ) X ON T1.ID = X.ID

    ReplyCancel
  5. Satish Kumar
    April 6, 2020 at 8:41 am

    SELECT
    T1.ID,
    COALESCE(X.NAME+CAST(X.RN AS VARCHAR(10)),T1.NAME) AS [NAME]
    FROM
    #tbl_DuplicateString T1
    LEFT JOIN
    (
    SELECT
    *
    FROM
    (
    SELECT
    ID,NAME,
    ROW_NUMBER()over(partition by name order by id) AS RN,
    COUNT(1) OVER(PARTITION BY NAME) CNT
    FROM
    #tbl_DuplicateString

    ) Y
    WHERE
    CNT >1

    ) X ON T1.ID = X.ID

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

November 5, 2017 5 Comments SQL Puzzle, , , , , , , , , ,