Remove duplicate while concatenating other columns values

Question

My data table looks as below (Table1)

            Table1
            ------------------------------------------
            ID  | IPaddress     | Port  | Value
            ----|---------------|-------|-------------      
            1   | xx.yy.14.15   | 332   | This is good
            ------------------------------------------      
            2   | xx.yy.14.15   | 332   | I can work
            ------------------------------------------
            3   | xx.yy.12.12   | 400   | Looks ok
            ------------------------------------------
            4   | xx.yy.12.12   | 400   | can I work
            ------------------------------------------
            5   | xx.yy.12.12   | 400   | Yes, please
            -------------------------------------------
            6   | xx.yy.14.16   | 401   | How is this
            -------------------------------------------
            7   | xx.yy.14.16   | 401   | Looks ok
            -------------------------------------------
            8   | xx.yy.14.16   | 401   | can I work
            -------------------------------------------
            9   | xx.yy.14.16   | 401   | Yes, please
            -------------------------------------------

The desired result table:

            ID  | IPaddress     | Port  | Value
            ----|---------------|-------|-----------------------------------------------------------        
            1   | xx.yy.14.15   | 332   | This is good and I can work
            --------------------------------------------------------------------------------------      
            2   | xx.yy.12.12   | 400   | Looks ok and can I work and Yes, please
            ---------------------------------------------------------------------------------------
            3   | xx.yy.14.16   | 401   | How is this and Looks ok and can I work and Yes, please
            ---------------------------------------------------------------------------------------

Here is what have I tried:

            DECLARE @VAR1 VARCHAR(50)
            DECLARE @VAR2 VARCHAR(50)

            SELECT @VAR1 = T1.VALUE,@VAR2=T2.VALUE
            FROM TABLE1 AS T1 INNER JOIN TABLE1 AS T2 ON T1.ID =T2.ID
            WHERE T1.IPADDRESS =T2.IPADDRESS

            SELECT IPADDRSS,PORT,@VAR1 + ' AND ' +@VAR2 FROM
              SELECT T1.*,
              ROW_NUMBER() OVER (PARTITION BY T1.IPADDRESS,T1.PORT ORDER BY VALUE) AS NM
              FROM TABLE1 AS T1
              )TBL
            WHERE NM = 1

However, from the above query, I'm able to achieve the desired output if there are only 2 duplicate rows (NOTE: here I'm considering IPADDRESS and PORT as duplicate, while other columns are not duplicate).

However, how can I achieve my desired result when the there are same IPADDRESS and PORT are there in 3 or 4 or 5 rows ? Please note that, the number of rows having same IPADDRESS and PORT is dynamic and it may go more than 10 sometime.

So, how can I handle such dynamic situation while getting my desired result?

Philip Kelley · Accepted Answer · 2019-04-25 18:23:29Z

2

Just saw your comment re: SQL 2017. Shoulda said that up front, I've been working on an old-school aggregation technique that was griefing me. SQL 2017 gave us the long-overdue string_agg function, which makes it dead easy:

SELECT
   row_number() over (order by IPaddress, Port) ID
  ,IPaddress
  ,Port
  ,string_agg(Value, ' and ')
 from Table1
 group by 
   IPaddress
  ,Port

You may need to poke at it a bit if ordering is critical.

@KeithL's version works too, with a bit of debugging...which I see you've just worked out. Me, I just don't like XML very much, is why I was working on an alternate.

answered Apr 25, 2019 at 18:23

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AskMe Over a year ago

Just going through the "string_agg". Good to know this.

KeithL · Accepted Answer · 2019-04-25 19:48:36Z

1

This is an answer for creating a delimited list (usually a comma but in your case ' and ' delimited)

try using a stuff into that column

select rownumber() over (order by IPAddress,Port),IPAddress,Port
    ,stuff((select ' and ' + value
            from table t2
            where t1.IPaddress=t2.ipaddress and t1.port=t2.port
            order by ID
            for XML path(''), type
            ).value('.','nvarchar(max)')
            ,1,len(' and '),'') as verbage
from table t1
group by IPAddress,Port

The way this works:

the outer query basically gets you a grouped result on IPAddress,Port

the correlated subquery provides a delimited list of each line associated with IPAddress,Port

the stuff logic is to remove the first ' and '

edited Apr 25, 2019 at 19:48

answered Apr 25, 2019 at 17:46

KeithL

5,6193 gold badges23 silver badges30 bronze badges

4 Comments

AskMe Over a year ago

Getting error : "The stuff function requires 4 argument(s)". Note: right now I'm using SSMS and SQL Server 2017. Moreover, could you please provide more detailed information on, how does this query works/ query flow?

AskMe Over a year ago

I believe, the len will take one parameter and the in your query there are 2 parameters. The last parameter should come out of len and should be used for stuff. Nice, will try to find how for XML path is working by the way.

KeithL Over a year ago

sorry. i just typed this into the answer and didn't check it. i will update the content

KeithL Over a year ago

I missed a paren in the len(' and ')

Ravi Sharma · Accepted Answer · 2019-04-26 11:42:09Z

1

SELECT
    t1.IpAddress,t1.port,

    value = STUFF((
        SELECT ' and ' + t2.value
        FROM Table1 t2
        WHERE t1.port = t2.port
        FOR XML PATH('')
    ),2, 3, '')
FROM Table1 t1
GROUP BY t1.port,t1.IpAddress

answered Apr 26, 2019 at 11:42

Ravi Sharma

3801 silver badge5 bronze badges

Comments

Red Devil · Accepted Answer · 2019-04-26 06:21:21Z

0

You can try this:

select t.* from (
select ROW_NUMBER() over (partition by port order by port) rn,id,port,value=stuff ( 
                         ( select ' ' + value from @t t
                         where t.port=t1.port
                                    for xml path ('')),1,1,''

                         ) from @t t1
                         group by id,port
) t
where rn=1

answered Apr 26, 2019 at 6:21

Red Devil

2,4135 gold badges30 silver badges46 bronze badges

Collectives™ on Stack Overflow

Remove duplicate while concatenating other columns values

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related