SQL Server Concatenate the 'EmployeeId' fields with separated by a comma

sqougxex 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(135)

-- Create a sample table

`CREATE TABLE #YourTable (
    RowID INT PRIMARY KEY,
    column1 VARCHAR(255),
    column2 VARCHAR(255),
    column3 VARCHAR(255),
    AssociatedRows VARCHAR(MAX)
);`

-- Insert sample data

`INSERT INTO #YourTable (RowID, column1, column2, column3, AssociatedRows)
VALUES
    (1, 'A', 'B', 'C', ''),
    (2, 'D', 'E', 'F', ''),
    (3, 'A', 'X', 'Y', ''),
    (4, 'G', 'H', 'I', ''),
    (5, 'J', 'K', 'C', '');`

Expected output:

RowID | column1| column2| column2| AssociatedRows
----------------------------------------------
1     | A      | B      | C      | 1,3,5
2     | D      | E      | F      | 2
3     | A      | X      | Y      | 1,3,5
4     | G      | H      | I      | 4
5     | J      | K      | C      | 1,3,5

Row IDs 1, 3, and 5 are associated with each other because they share matching values in at least one field, and their associated row IDs are stored in the AssociatedRows column.

I tried below code but didn't achieve the expected output -

; WITH Addressdups(EmployeeAddressClean, EmployeeCityClean, EmployeeStateZipClean, EmployeeNumber)
AS (
    SELECT EmployeeAddressClean, EmployeeCityClean, EmployeeStateZipClean , 
    EmployeeNumber = stuff( (Select ', ' + EmployeeNumber 
                           from #Employee e2 
                           Where e1.EmployeeAddressClean = e2.EmployeeAddressClean
                           and e1.EmployeeCityClean = e2.EmployeeCityClean
                           and e1.EmployeeStateZipClean = e2.EmployeeStateZipClean   for xml path ('')),1,2,'') 
    FROM #Employee e1   Group By EmployeeAddressClean, EmployeeCityClean, EmployeeStateZipClean  Having count(distinct e1.EmployeeNumber ) > 1
   ),
   SSNDups (SSN , EmployeeNumber)
   AS 
   ( SELECT SSN , EmployeeNumber = stuff( (Select ', ' + EmployeeNumber 
                           from #Employee e2 
                           Where e1.SSN = e2.SSN
                             for xml path ('')),1,2,'') 
    FROM #Employee e1   Group By SSN  Having count(distinct e1.EmployeeNumber ) > 1
    ),
     NameDups (EmployeeName , EmployeeNumber)
   AS 
   ( SELECT EmployeeName , EmployeeNumber = stuff( (Select ', ' + EmployeeNumber 
                           from #Employee e2 
                           Where e1.EmployeeName = e2.EmployeeName
                             for xml path ('')),1,2,'') 
    FROM #Employee e1   Group By EmployeeName  Having count(distinct e1.EmployeeNumber ) > 1 
    )
   Select e1.* , concat_ws( ',', a.EmployeeNumber , s.EmployeeNumber , n.EmployeeNumber) duplicate
   from #Employee e1
   Left Join Addressdups a on  e1.EmployeeAddressClean = a.EmployeeAddressClean
                           and e1.EmployeeCityClean =    a.EmployeeCityClean
                           and e1.EmployeeStateZipClean = a.EmployeeStateZipClean
   Left Join SSNDups s on e1.SSN = s.SSN
   Left Join NameDups n On e1.EmployeeName = n.EmployeeName

sql-server

来源：https://stackoverflow.com/questions/77147955/concatenate-the-employeeid-fields-with-separated-by-a-comma

2条答案

按热度按时间

x8goxv8g1#

You can try following option

Based on the given example data and its expected output, understand that need to find associated rows by searching multiple levels recursively.

Create a scalar function which will return associated rows with comma separated for given input RowID. The function also takes another input parameter @MaxSearchLvel which tells how many levels the associated rows can be searched, defined 3 as a default value.

CREATE OR ALTER FUNCTION dbo.getAssociatedRows(@RowID INT, @MaxSearchLevel TINYINT = 3)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @level INT =1
    DECLARE @retVal VARCHAR(MAX)

    ;WITH AssociationCTE AS
    (
        SELECT @level AS levels, t.RowID, t.column1, t.column2, t.column3
        FROM YourTable t
        WHERE RowID = @RowID
    
        UNION ALL

        SELECT levels+1 AS levels, t.RowID, t.column1, t.column2, t.column3
        FROM YourTable t
        INNER JOIN AssociationCTE cte ON cte.column1 = t.column1 OR cte.column2 = t.column2 OR cte.column3 = t.column3
        WHERE t.RowID <> cte.RowID AND cte.levels < @MaxSearchLevel 
    )
    SELECT @retVal = STUFF(
        (SELECT DISTINCT ',' + CONVERT(VARCHAR,RowID) 
        FROM AssociationCTE FOR XML PATH(''))   ,1,1,'')
    OPTION (MAXRECURSION 255)

    RETURN @retVal
END
GO

Select query to view associated rows for the example data

SELECT RowID, column1, column2, column3, dbo.getAssociatedRows(RowID, DEFAULT) AS AssociatedRows
FROM YourTable

The output of the above query will match exactly the given expected output. Please note that performance of the query is question mark.

赞(0）回复(0）举报 2023-10-15

uxh89sit2#

select
    yt.RowID,
    yt.column1,
    yt.column2,
    yt.column3,
    stuff(
            (
                select ',' + convert(varchar,yt2.RowID)
                from #YourTable yt2
                where yt.column1 = yt2.column1
                   or yt.column2 = yt2.column2
                   or yt.column3 = yt2.column3
                group by yt2.RowID
                order by yt2.RowID
                for xml path('')
            ), 1, 1, ''
        ) as AssociatedRows
from #YourTable yt

Gives expected output:

/------------------------------------------------------\
| RowID | column1 | column2 | column3 | AssociatedRows |
|-------|---------|---------|---------|----------------|
|   1   |    A    |    B    |    C    |     1,3,5      |
|   2   |    D    |    E    |    F    |     2          |
|   3   |    A    |    X    |    Y    |     1,3        |
|   4   |    G    |    H    |    I    |     4          |
|   5   |    J    |    K    |    C    |     1,5        |
\------------------------------------------------------/

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Concatenate the 'EmployeeId' fields with separated by a comma

2条答案

相关问题

热门标签

最新问答