CODEWITHSUNDEEP
sqlsql-server-2005pivot
Viaceslav Dymarski
Viaceslav Dymarski

Reputation: 23

SQL query to join values from multiple records

I've got a table Employees with employees (say the important fields are ID int, Name varchar(50)) and a table Areas with sales areas (ID int, EmployeeID int, USState char(2)).

The sample values are:

Employees
ID     Name
1      Shoeman
2      Smith
3      Johnson

Areas
ID     EmployeeID     USState
1      1              NY
2      1              FL
3      1              AR
4      2              DC
5      2              AR
6      3              TX

Can anyone give me a hint on making a SQL query to get the output recordset in the following way:

EmployeeID     USState
1              NY FL AR
2              DC AR
3              TX

Target platform: SQL Server 2005.

Upvotes: 2

Views: 487

Answers (2)

Darren
Darren

Reputation: 11

This is my perfered format to use a UDF (appears to be much faster as well on a large DB)

CREATEFUNCTION dbo.StateList(@ID int) RETURNS varchar(max)

AS

DECLARE @out varchar(max);

SET @out=''; -- comment this out to reutrn NULL if nothing found

SELECT @out=@out+USState+' ' -- operates like a loop assigning the values in sequence to the out variable.

FROM AREAS
WHERE EmployeeID=@ID
ORDER BY USState

RETURN @out

Query example ...

SELECT Name, dbo.StateList(EmployeeID)
FROM Employees
Order BY Name

In this manner you create one UDF for each list you wish to explode in this way.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838336

This operation is called GROUP_CONCAT in MySQL but SQL Server doesn't support it.

In SQL Server you can simulate the same functionality using the FOR XML PATH hack.

SELECT extern.EmployeeID, states AS USState
FROM Areas AS extern
CROSS APPLY (
    SELECT USState + ' '
    FROM Areas AS intern
    WHERE extern.EmployeeID = intern.EmployeeID
    FOR XML PATH('')
) T1 (states)
GROUP BY extern.EmployeeID, states
ORDER BY extern.EmployeeID

An alternative is to use a recursive CTE. This is a less hacky solution but it is also more complicated:

WITH qs(EmployeeID, USState, rn, cnt) AS
(
    SELECT
        EmployeeID,
        USState,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY USState),
        COUNT(*) OVER (PARTITION BY EmployeeID)
    FROM    Areas
),
t (EmployeeID, prodname, gc, rn, cnt) AS
(
    SELECT EmployeeID, USState, CAST(USState AS NVARCHAR(MAX)), rn, cnt
    FROM qs
    WHERE rn = 1
    UNION ALL
    SELECT
        qs.EmployeeID, qs.USState,
        CAST(t.gc + ' ' + qs.USState AS NVARCHAR(MAX)),
        qs.rn, qs.cnt
    FROM t
    JOIN qs ON qs.EmployeeID = t.EmployeeID
           AND qs.rn = t.rn + 1
)
SELECT EmployeeID, gc AS USState
FROM   t
WHERE  rn = cnt
ORDER BY EmployeeID
OPTION (MAXRECURSION 0)

Both methods give the result you want:

EmployeeID     USState
1              NY FL AR 
2              DC AR 
3              TX

Upvotes: 2

Related Questions