正确的答案是修复数据模型。
为了得到想要的结果，你可以

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;

在线演示

您可以通过使用 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')

主查询

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

用于更新查询

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

请使用下面的查询，

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

使用材料的建议是一个很好的解决办法。如果您的版本是sql server 2017或更高版本，则可以使用更简单的de function string\ u 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