Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Generating a Distinct Delimited List Using XML Expand / Collapse
Author
Message
Posted Monday, June 28, 2010 9:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:00 PM
Points: 230, Visits: 237
Comments posted to this topic are about the item Generating a Distinct Delimited List Using XML
Post #944361
Posted Monday, June 28, 2010 11:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 29, 2010 4:31 AM
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)
Post #944380
Posted Monday, June 28, 2010 11:20 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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.
Post #944382
Posted Monday, June 28, 2010 11:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:00 PM
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
Post #944396
Posted Tuesday, June 29, 2010 12:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:00 PM
Points: 230, Visits: 237
Thanks Mike for new approach.
Post #944398
Posted Tuesday, June 29, 2010 2:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 8:46 AM
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
Post #944483
Posted Tuesday, June 29, 2010 3:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 2,397, Visits: 3,417
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"
Post #944508
Posted Tuesday, June 29, 2010 7:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 411, Visits: 1,406
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?
Post #944618
Posted Tuesday, June 29, 2010 8:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 6:34 AM
Points: 554, Visits: 1,200
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

Post #944679
Posted Tuesday, June 29, 2010 8:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 7:54 AM
Points: 116, Visits: 99
--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

Post #944705
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse