Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generating a Distinct Delimited List Using XML


Generating a Distinct Delimited List Using XML

Author
Message
Puja Shah
Puja Shah
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 237
Comments posted to this topic are about the item Generating a Distinct Delimited List Using XML
Kailash Mishra
Kailash Mishra
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 66
Appreciate your article.

This is with lot of explanation, but we can achieve the output more efficient way with the sorting of rolename also:

select distinct a.userid, substring(b.rolename, 1, len(b.rolename )-1) from @UserRole a
cross apply (select distinct RoleName + ', ' from @UserRole where a.userid = userid
for xml path('')) b (rolename)
Mike C
Mike C
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 1168

WITH CTE
AS
(
SELECT ROW_NUMBER() OVER
(
PARTITION BY u2.UserID
ORDER BY MIN(u2.RoleAssignedDate)
) AS RowNum,
u2.UserID,
u2.RoleName
FROM @UserRole u2
GROUP BY u2.UserID,
u2.RoleName
)
SELECT DISTINCT u1.UserID,
SUBSTRING
(
(
SELECT ', ' + c.RoleName AS '*'
FROM CTE c
WHERE u1.UserID = c.UserID
ORDER BY c.RowNum
FOR XML PATH('')
), 3, 8000
) AS Roles
FROM @UserRole u1;



Should give the same result without the XQuery.
Puja Shah
Puja Shah
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 237
Thanks Kailash Mishra.

The query that you gave will give result in an alphabetical order instead of the order in which the roles were assigned. The article is about retrieving delimited values in the order they were stored.


Thanks & regards,
Puja
Puja Shah
Puja Shah
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 237
Thanks Mike for new approach. :-)
nick.hanson
nick.hanson
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 169
I like this article, it was a problem I had to solve previously.
I particularly like the way you solved the problem of the final ',' at the end of the string. Very neat.

I have to say I was totally lost by the end of your explanation and found the code too difficult to follow.

The following is an adaptation of your 2nd stage code, but using Group By RoleName which then allows Order By MIN(RoleAssignedDate) and thereby giving the order required.

 
SELECT DISTINCT u1.UserID,
REPLACE (
(SELECT u2.RoleName + ',' AS 'data()'
FROM @UserRole u2
WHERE u2.UserID = u1.UserID
GROUP BY RoleName
ORDER BY MIN(RoleAssignedDate)
FOR XML PATH('')
) + '$', ',$', ''
) AS Roles
FROM @UserRole u1



Cheers,
Nick
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431
Let's do some testing...
SET NOCOUNT ON

-- Prepare sample data
DECLARE @UserRole TABLE
(
UserID INT NOT NULL,
RoleName VARCHAR(100) NOT NULL,
ProjectID INT NOT NULL,
RoleAssignedDate DATETIME NOT NULL
)

-- Populate sample data
INSERT @UserRole
(
UserID,
RoleName,
ProjectID,
RoleAssignedDate
)
VALUES (1112, 'Technical Director', 2041, '1967-02-20 04:21:13.490'),
(1357, 'Training', 1614, '1961-09-14 16:18:59.990'),
(1836, 'Technical Director', 1628, '1987-07-30 11:22:45.060'),
( 715, 'Accounting', 1487, '1995-01-08 11:46:17.670'),
( 162, 'Approver', 1548, '2001-07-02 11:34:14.260'),
(1975, 'Technical Director', 1614, '1955-11-10 03:55:05.560'),
(1112, 'System Administrator', 831, '1956-04-25 08:26:54.040'),
( 162, 'Technical Director', 986, '1989-12-17 15:51:04.330'),
( 715, 'Accounting', 461, '1954-11-27 00:45:52.830'),
(1357, 'Developer', 2064, '2006-12-15 23:43:55.470'),
(1867, 'Technical Director', 1416, '2003-04-11 16:50:01.070'),
(1975, 'Developer', 1548, '1967-05-11 17:01:26.840'),
(1975, 'Accounting', 1089, '1988-06-20 00:52:16.070'),
( 162, 'Marketing', 1443, '1995-06-10 14:29:23.290'),
(1112, 'Accounting', 2109, '1999-12-05 09:07:46.620'),
( 162, 'Technical Director', 1089, '1975-09-25 12:13:12.590'),
( 162, 'International Sales Manager', 1628, '1984-05-30 09:25:18.330'),
(1836, 'Technical Customer', 420, '1993-05-28 00:49:31.090'),
(1357, 'Technical Director', 2036, '1979-04-25 23:38:48.120'),
(1836, 'Developer', 1628, '2006-07-26 20:36:53.420')

-- Start timing
SET STATISTICS IO ON
SET STATISTICS TIME ON

-- 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

-- Puja Shah
PRINT '*** Puja Shah ******************************************************************'

SELECT b.UserID,
LEFT(b.Roles, LEN(b.Roles) - CHARINDEX(',', REVERSE(b.Roles))) AS Roles
FROM (
SELECT DISTINCT a.UserID,
CAST(a.Roles.query('distinct-values(/Root/Roles)') AS VARCHAR(MAX)) AS Roles
FROM (
SELECT u1.UserID,
CAST(
(
SELECT DISTINCT u2.RoleName + ',' AS Roles,
ROW_NUMBER() OVER(PARTITION BY u2.UserID ORDER BY u2.RoleAssignedDate) AS RID
FROM @UserRole AS u2
WHERE u2.UserID = u1.UserID
ORDER BY RID
FOR XML PATH(''),
ROOT('Root')
) AS XML
) AS Roles
FROM @UserRole AS u1
) AS a
) AS b
ORDER BY b.UserID

-- Kailash Mishra <- Fails ordering
PRINT '*** Kailash Mishra *************************************************************'

SELECT DISTINCT a.UserID,
SUBSTRING(b.RoleName, 1, LEN(b.RoleName) -1)
FROM @UserRole AS a
CROSS APPLY (
SELECT DISTINCT b.RoleName + ','
FROM @UserRole AS b
WHERE b.UserID = a.UserID
FOR XML PATH('')
) AS b(RoleName)
ORDER BY a.UserID

-- Mike C
PRINT '*** Mike C *********************************************************************'

;WITH cte
AS (
SELECT ROW_NUMBER() OVER (PARTITION BY u2.UserID ORDER BY MIN(u2.RoleAssignedDate)) AS RowNum,
u2.UserID,
u2.RoleName
FROM @UserRole AS u2
GROUP BY u2.UserID,
u2.RoleName
)
SELECT DISTINCT u1.UserID,
SUBSTRING(
(
SELECT ',' + c.RoleName AS '*'
FROM cte AS c
WHERE u1.UserID = c.UserID
ORDER BY c.RowNum
FOR XML PATH('')
), 2, 8000
) AS Roles
FROM @UserRole AS u1
ORDER BY u1.UserID

-- Nick Hanson
PRINT '*** Nick Hanson ****************************************************************'

SELECT DISTINCT u1.UserID,
REPLACE (
(
SELECT u2.RoleName + ',' AS 'data()'
FROM @UserRole AS u2
WHERE u2.UserID = u1.UserID
GROUP BY u2.RoleName
ORDER BY MIN(u2.RoleAssignedDate)
FOR XML PATH('')
) + '$', ',$', ''
) AS Roles
FROM @UserRole AS u1
ORDER BY u1.UserID

-- Stop timing
SET STATISTICS TIME OFF
SET STATISTICS IO OFF




N 56°04'39.16"
E 12°55'05.25"
R.P.Rozema
R.P.Rozema
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 1681
Another variation on the previous suggestions. See what happens to all the other solutions when any of the roles is changed into for example 'R&D'. Plus, I don't like the replace()-approach to get rid of the last ','. I rather don't generate it if it's not needed instead of taking it off in an additional processing step:
SELECT 
u1.UserID,
(
select case row_number() over (order by (select 1)) when 1 then '' else ', ' end
+ t.RoleName AS [text()]
from (
select row_number() over (order by min(u2.RoleAssignedDate)) as nr, u2.RoleName
from @UserRole u2
where u2.UserID = u1.UserID
group by u2.RoleName
) t
order by t.nr
for xml path(''), type
).value('.', 'nvarchar(max)') as Roles
from (
select
UserID
from @UserRole
group by
UserID
) u1
order by u1.UserID





Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Bradley Deem
Bradley Deem
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 1248
I recommend using a CLR for this. Microsoft has a great example here http://msdn.microsoft.com/en-us/library/ms131056.aspx. I'm curious what the performance differences are. Admittedly, I have not compared the two because I've never had the CLR function not perform adequately.

You can then use this as an aggregate, for example.

SELECT dbo.List(myColumn)
FROM myTable
GROUP BY SomeOtherColumn


Brian Barkauskas
Brian Barkauskas
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 169

--An alternative without ROW_NUMBER()
--How about this?
select distinct
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search