Get distinct values of two columns and merge into rows

Question

I have a table named dbo.WebsiteIP having two columns IPAddress, SiteName, and I am doing a bulk insert the values to the this table which look like below

IPAddress       SiteName
192.168.30.6    website1.domain.com
192.168.30.6    website2.domain.com
192.168.30.7    website3.domain.com
192.168.30.7    website4.domain.com
192.168.30.7    website5.domain.com
192.168.30.7    website6.domain.com
192.168.30.7    website7.domain.com
192.168.30.8    website8.domain.com
192.168.30.8    website9.domain.com
192.168.30.8    website10.domain.com
192.168.30.8    website11.domain.com
192.168.30.9    website12.domain.com
192.168.30.8    website13.domain.com
192.168.30.8    website14.domain.com
192.168.30.24   website15.domain.com
192.168.30.8    website16.domain.com
192.168.30.8    website17.domain.com

I want to do a distinct query for the IP Address and combine the SiteName like below

IPAddress       WebsiteName 
192.168.30.6    website1, website2,
192.168.30.7    website3, website4, website5, website6, website7
192.168.30.8    website8, website9, website10, website11, website13, website14
192.168.30.9    website12
192.168.30.24   website15

I can able to combine the IPAddress using the below query but how to combine the appropriate Sitename to the IP Address.

Update Table1
Set IP= (Select IPAddress + ',' + ' '
From dbo.WebsiteIP
GROUP BY IPAddress FOR XML PATH(''))
GO

Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) and just don't. Keep the schema normalized. — sticky bit, May 30 '20 at 11:18
I agree. If you want this data as a csv, leave it in the db as it is and GROUP_CONCAT it every time you want it as a csv — Caius Jard, May 30 '20 at 11:21

score 2 · Accepted Answer · answered May 30 '20 at 11:52

2

The right answer to this is fixing your data modal.

To get the desired result, you can do

SELECT T.IpAddress,
       STUFF(
              (
                SELECT ',' + LEFT(WebsiteName, CHARINDEX('.', WebsiteName)-1)
                FROM Data TT
                WHERE TT.IpAddress = T.IpAddress
                FOR XML PATH('')
              )
              , 1, 1, ''
            ) Result
FROM Data T
GROUP BY T.IpAddress;

Online Demo

answered May 30 '20 at 11:52

Ilyes

14,640
4
29
55

This Query works like a charm, thanks for the help. will test in the staging machine – T.Anand May 30 '20 at 14:26

score 1 · Answer 2 · answered May 30 '20 at 11:56

You can achieve this by using STUFF

DECLARE @T Table(
IP VARCHAR(MAX),
WEBSITE VARCHAR(MAX))


INSERT INTO @T VALUES('192.168.30.6','website1.domain.com')
INSERT INTO @T VALUES('192.168.30.6','website2.domain.com')
INSERT INTO @T VALUES('192.168.30.7','website3.domain.com')
INSERT INTO @T VALUES('192.168.30.7','website4.domain.com')
INSERT INTO @T VALUES('192.168.30.7','website5.domain.com')
INSERT INTO @T VALUES('192.168.30.7','website6.domain.com')
INSERT INTO @T VALUES('192.168.30.7','website7.domain.com')
INSERT INTO @T VALUES('192.168.30.8','website8.domain.com')
INSERT INTO @T VALUES('192.168.30.8','website9.domain.com')
INSERT INTO @T VALUES('192.168.30.8','website10.domain.com')
INSERT INTO @T VALUES('192.168.30.8','website11.domain.com')
INSERT INTO @T VALUES('192.168.30.9','website12.domain.com')
INSERT INTO @T VALUES('192.168.30.8','website13.domain.com')
INSERT INTO @T VALUES('192.168.30.8','website14.domain.com')
INSERT INTO @T VALUES('192.168.30.24','website15.domain.com')
INSERT INTO @T VALUES('192.168.30.8',' website16.domain.com')
INSERT INTO @T VALUES('192.168.30.8',' website17.domain.com')

Main Query

SELECT M.IP,STUFF (( Select ','+WEBSITE 
From @T S WHERE S.IP=M.IP
FOR XML PATH('')),1,1,'') 
FROM @T M GROUP BY M.IP

For Update Query

 update Table1 T1 set IP = T2.SiteName
 from(SELECT M.IP,STUFF (( Select ','+WEBSITE 
 From @T S WHERE S.IP=M.IP
 FOR XML PATH('')),1,1,'')  AS Site
 FROM @T M GROUP BY M.IP
 ) S ON S.IP=T1.IP

score 0 · Answer 3 · answered May 30 '20 at 11:33

0

Please use below query,

update Table1 T1 set IP = T2.SiteName
from
(select IPAddress, string_agg(SiteName, '') as SiteName from dbo.WebsiteIP 
) T2
where T2.IPAddress =  T1.IPAddress;

answered May 30 '20 at 11:33

Jim Macaulay

4,709
4
28
53

i am using SQL Server 2012 and 2016, the String_agg is not support in my version. – T.Anand May 30 '20 at 11:44

score 0 · Answer 4 · answered May 30 '20 at 13:04

The suggestions of using stuff is a good solution. If your version is SQL server 2017 or higher you can use the much simpler syntax of de function STRING_AGG.

DECLARE @T Table(
IP VARCHAR(MAX),
WEBSITE VARCHAR(MAX))

INSERT INTO @T 
VALUES    ('192.168.30.6','website1.domain.com')
        , ('192.168.30.6','website2.domain.com')
        , ('192.168.30.7','website3.domain.com')
        , ('192.168.30.7','website4.domain.com')
        , ('192.168.30.7','website5.domain.com')
        , ('192.168.30.7','website6.domain.com')
        , ('192.168.30.7','website7.domain.com')
        , ('192.168.30.8','website8.domain.com')
        , ('192.168.30.8','website9.domain.com')
        , ('192.168.30.8','website10.domain.com')
        , ('192.168.30.8','website11.domain.com')
        , ('192.168.30.9','website12.domain.com')
        , ('192.168.30.8','website13.domain.com')
        , ('192.168.30.8','website14.domain.com')
        , ('192.168.30.24','website15.domain.com')
        , ('192.168.30.8',' website16.domain.com')
        , ('192.168.30.8',' website17.domain.com');


SELECT t.IP, STRING_AGG(WEBSITE, ',') AS Websites
From @T t
GROUP BY t.IP

Get distinct values of two columns and merge into rows

4 Answers4