CODEWITHSUNDEEP
sql-servert-sql
realPro
realPro

Reputation: 1846

TSQL: Merge two sets with grouping and concatenation on the values

I have two tables with 2+ million rows in each. I need to merge them and the performance is very important.

The flags need to get concatenated after merge.

Table 1

ip       flags
-----------
23.4.2.8  x
94.4.7.3  t
12.5.7.9  x
33.1.2.3  xc

Table 2

ip       flags
-----------
23.4.2.8  y
94.4.7.3  t
99.6.7.9  t

Table 3 - merged flags on both tables

ip       flags
-----------
23.4.2.8  yx  -- merged flags, the order of flags is not important
94.4.7.3  t   -- merged flags, but distinct
12.5.7.9  x
33.1.2.3  xc
99.6.7.9  t

I have this working in C# and wonder if it is possible to do this using SQL. Is there a SQL command that can do this in efficient way?

Upvotes: 1

Views: 79

Answers (4)

realPro
realPro

Reputation: 1846

Expanding the answer by ssn to clean the duplicate characters. The performance is really good!

SELECT ISNULL(t.IP, t2.IP) AS IP
    , [dbo].[FN_CLEANDUPCHAR](CONCAT(ISNULL(t.block_reason_code,''), ISNULL(t2.block_reason_code, ''))) AS block_reason_code 
FROM ##table1 as t FULL JOIN ##table2 as t2 
ON t.IP = t2.IP

And this is the function:

CREATE FUNCTION [dbo].[FN_CLEANDUPCHAR]
(
    @S varchar(20)
)
RETURNS varchar(20)
AS
BEGIN
    SELECT
        @S = CASE WHEN CHARINDEX(SUBSTRING(@s,Number,1),@s) BETWEEN 1 AND Number-1 THEN STUFF(@s,Number,1,'') ELSE @S END
    FROM master.dbo.spt_values
    WHERE Number BETWEEN 2 AND LEN(@s) AND type='P'
    ORDER BY Number DESC

    return @S    
END
GO

Upvotes: 0

ssn
ssn

Reputation: 509

Not sure how wide your tables are, but something like below could work:

SELECT ISNULL(t.IP, t2.IP) AS IP
    , CONCAT(ISNULL(t.flags,’’), ISNULL(t2.flags, ‘’)) AS flags
FROM table1 as t
FULL JOIN table2 as t2 ON t.IP = t2.IP

Upvotes: 1

BJones
BJones

Reputation: 2460

Another option if you don't have SQL Server 2017+ using STUFF and FOR XML PATH() - I think it's good for 2008 +

Here's a SQL Fiddle

SELECT 
    ip
    ,STUFF((SELECT  ',' + t.flags
            FROM(
                SELECT ip, flags 
                FROM table1
                UNION ALL
                SELECT ip, flags 
                FROM table2
                )t
            WHERE t.ip = a.ip
        FOR XML PATH('')), 1, 1, '') AS Flags
FROM
(
    SELECT ip, flags 
    FROM table1
    UNION ALL
    SELECT ip, flags 
    FROM table2
)a
GROUP BY ip

Upvotes: 2

Eli
Eli

Reputation: 2608

You didn't mention which version of SQL Server you're using, though if it is 2017+ this solution will work for you.
You can accomplish your goal using STRING_AGG. Try the following with your data (create tables based off of your sample data above)

CREATE TABLE #table1(
    ip VARCHAR(100)
    ,flags CHAR(10)
)
GO
CREATE TABLE #table2(
    ip VARCHAR(100)
    ,flags CHAR(10)
)
GO

INSERT INTO #table1 VALUES 
('23.4.2.8','x')
,('94.4.7.3','t')
,('12.5.7.9','x')
,('33.1.2.3','xc')
GO 

INSERT INTO #table2 VALUES 
 ('23.4.2.8','y')
,('94.4.7.3','t')
,('99.6.7.9','t')

GO 

SELECT 
    ip
    ,STRING_AGG(RTRIM(flags),',')
FROM
(
    SELECT 
        ip
        ,flags 
    FROM #table1
    UNION ALL
    SELECT 
        ip
        ,flags 
    FROM #table2
)a
GROUP BY ip

Upvotes: 1

Related Questions