Generating a Distinct Delimited List Using XML

--An alternative without ROW_NUMBER()
--How about this?
select
u.UserID
, stuff((
select ', ' + u2.RoleName
from @UserRole u2
where u2.UserID = u.UserID
and u2.RoleAssignedDate = (
select min(u3.RoleAssignedDate)
from @UserRole u3
where u3.UserID = u2.UserID
and u3.RoleName = u2.RoleName
)
order by u2.RoleAssignedDate asc
for xml path('')
), 1, 2, '') 'RoleName'
from @UserRole u
group by u.UserID

select
u.UserID
, stuff((
select ', ' + u2.RoleName
from @UserRole u2
where u2.UserID = u.UserID
group by u2.RoleName
order by min(u2.RoleAssignedDate)
for xml path('')
),1,2,'') 'RoleName'
from @UserRole u
group by u.UserID

Brian Barkauskas (6/29/2010)

OK, then use GROUP:

--An alternative without ROW_NUMBER()
--How about this?
select
        u.UserID
        , stuff((
                select ', ' + u2.RoleName
                from @UserRole u2
                where u2.UserID = u.UserID
                and u2.RoleAssignedDate = (
                        select min(u3.RoleAssignedDate)
                        from @UserRole u3
                        where u3.UserID = u2.UserID
                        and u3.RoleName = u2.RoleName
                )
                order by u2.RoleAssignedDate asc
                for xml path('')
        ), 1, 2, '') 'RoleName'
from @UserRole u
group by u.UserID

*** R.P.Rozema *****************************************************************

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#4F1BAE10'. Scan count 8, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
*** Brian Barkauskas ***********************************************************

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#4F1BAE10'. Scan count 8, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.

CREATE TABLE #Role
(
RoleName VARCHAR(100)
);
GO

INSERT INTO #Role
(
RoleName
)
VALUES ('Accounting'), ('Approver'), ('Developer'), ('International Sales Manager'), ('Marketing'), ('System Administrator'),
('Technical Customer'), ('Technical Director'), ('Training');
GO

CREATE TABLE #UserRole
(
UserID INT NOT NULL,
RoleName VARCHAR(100) NOT NULL,
ProjectID INT NOT NULL,
RoleAssignedDate DATETIME NOT NULL,
PRIMARY KEY
(
UserID,
RoleName,
ProjectID,
RoleAssignedDate
)
);
GO

CREATE TABLE #Numbers
(
Num INT NOT NULL PRIMARY KEY
);
GO

WITH DIGITS
AS
(
SELECT 0 AS Num
UNION ALL
SELECT Num + 1
FROM DIGITS
WHERE Num < 10
)
INSERT INTO #Numbers
(
Num
)
SELECT Num
FROM DIGITS;
GO

WITH NUMBERS
AS
(
SELECT HundredThousand.Num * 100000 + TenThousand.Num * 10000 +
Thousand.Num * 1000 + Hundred.Num * 100 +
Ten.Num + One.Num AS Num
FROM #Numbers HundredThousand
CROSS JOIN #Numbers TenThousand
CROSS JOIN #Numbers Thousand
CROSS JOIN #Numbers Hundred
CROSS JOIN #Numbers Ten
CROSS JOIN #Numbers One
),
RANDOMDATA
AS
(
SELECT Num AS UserID,
RoleName,
ABS(CHECKSUM(NEWID()) % 8000) + 1000 AS ProjectID,
DATEADD(DAY, -ABS(CHECKSUM(NEWID()) % 1000), GETDATE()) AS RoleAssignedDate,
NEWID() AS SortOrder
FROM NUMBERS
CROSS JOIN #Role
)
INSERT INTO #UserRole
(
UserID,
RoleName,
ProjectID,
RoleAssignedDate
)
SELECT TOP(1000000) UserID,
RoleName,
ProjectID,
RoleAssignedDate
FROM RANDOMDATA
ORDER BY SortOrder;
GO

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
UserID Roles
----------- -------------------------------------------------------------------------------------------------------------------------------------
1 Technical Director,Technical Customer
2 R&D,Approver
4 International Sales Manager,Marketing,System Administrator,Technical Customer

...

1111018 International Sales Manager,Technical Director
1111019 Approver,Marketing
1111020 Approver

(210092 row(s) affected)

Table '#UserRole___________________________________________________________________________________________________________0000000007DC'. Scan count 210093, logical reads 641750, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 7719 ms, elapsed time = 7917 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
UserID Roles
----------- -------------------------------------------------------------------------------------------------------------------------------------
1 Technical Director, Technical Customer
2 R&D, Approver
4 International Sales Manager, Marketing, System Administrator, Technical Customer

...

1111018 International Sales Manager, Technical Director
1111019 Approver, Marketing
1111020 Approver

(210092 row(s) affected)

Table '#UserRole___________________________________________________________________________________________________________0000000007DC'. Scan count 210095, logical reads 641749, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1210296, logical reads 3993980, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1509437 ms, elapsed time = 772965 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
UserID
----------- -------------------------------------------------------------------------------------------------------------------------------------
1 Technical Customer,Technical Director
2 Approver,R&D
4 International Sales Manager,Marketing,System Administrator,Technical Customer

...

1111018 International Sales Manager,Technical Director
1111019 Approver,Marketing
1111020 Approver

(210092 row(s) affected)

Table '#UserRole___________________________________________________________________________________________________________0000000007DC'. Scan count 210095, logical reads 641749, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1210312, logical reads 4167863, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 103482 ms, elapsed time = 54131 ms.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 13 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
UserID Roles
----------- -------------------------------------------------------------------------------------------------------------------------------------
1 Technical Director, Technical Customer
2 R&D, Approver
4 International Sales Manager, Marketing, System Administrator, Technical Customer

...

1111018 International Sales Manager, Technical Director
1111019 Approver, Marketing
1111020 Approver

(210092 row(s) affected)

Table '#UserRole___________________________________________________________________________________________________________0000000007DC'. Scan count 210093, logical reads 641750, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 20672 ms, elapsed time = 21298 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
UserID RoleName
----------- -------------------------------------------------------------------------------------------------------------------------------------
8 Marketing, Technical Director, Training, Accounting, Approver
9 Accounting, R&D, Developer
10 Accounting, R&D, Technical Customer, System Administrator, Training

...

1111014 Approver, Technical Director, Training, System Administrator
1111015 Developer, Training
1111016 Marketing, R&D

(210092 row(s) affected)

Table '#UserRole___________________________________________________________________________________________________________0000000007DC'. Scan count 974634, logical reads 2944527, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1210315, logical reads 4196120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 96921 ms, elapsed time = 51262 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
UserID RoleName
----------- -------------------------------------------------------------------------------------------------------------------------------------
1 Technical Director, Technical Customer
2 R&D, Approver
4 International Sales Manager, Marketing, System Administrator, Technical Customer

...

1111018 International Sales Manager, Technical Director
1111019 Approver, Marketing
1111020 Approver

(210092 row(s) affected)

Table '#UserRole___________________________________________________________________________________________________________0000000007DC'. Scan count 210093, logical reads 641750, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 8000 ms, elapsed time = 8233 ms.

-- Peso
PRINT '*** Peso ***********************************************************************'

SELECT b.UserID,
STUFF(f.Roles, 1, 1, '') AS Roles
FROM (
SELECT UserID
FROM #UserRole
GROUP BY UserID
) AS b
CROSS APPLY (
SELECT TOP(100) PERCENT
',' + w.RoleName
FROM #UserRole AS w
WHERE w.UserID = b.UserID
GROUP BY w.RoleName
ORDER BY MIN(RoleAssignedDate)
FOR XML PATH('')
) AS f(Roles)
ORDER BY b.UserID

PRINT '*** Peso & R.P.Rozema ****************************************************************'

SELECT b.UserID,
STUFF(f.Roles.value('.','nvarchar(max)'), 1, 1, '') AS Roles
FROM (
SELECT UserID
FROM #UserRole
GROUP BY UserID
) AS b
CROSS APPLY (
SELECT ',' + w.RoleName as [text()]
FROM #UserRole AS w
WHERE w.UserID = b.UserID
GROUP BY w.RoleName
ORDER BY MIN(RoleAssignedDate)
FOR XML PATH(''), type
) AS f(Roles)
ORDER BY b.UserID

PRINT '*** Peso & R.P.Rozema ****************************************************************'

SELECT b.UserID,
replace(replace(replace(STUFF(f.Roles, 1, 1, ''), '&', '&'), '<', '<'), '>', '>') AS Roles
FROM (
SELECT UserID
FROM #UserRole
GROUP BY UserID
) AS b
CROSS APPLY (
SELECT ',' + w.RoleName as [text()]
FROM #UserRole AS w
WHERE w.UserID = b.UserID
GROUP BY w.RoleName
ORDER BY MIN(RoleAssignedDate)
FOR XML PATH('')
) AS f(Roles)
ORDER BY b.UserID