Pivoting using STUFF and XML PATH

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivoting using STUFF and XML PATH

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2009-06-20 : 19:17:26

Hi there

I have 3 tables:

1) Users:
UserID INT,
Username VARCHAR(32)

2) UserInRoles:
UserID INT,
RoleID INT

3) Roles:
RoleID INT,
RoleName VARCHAR(32)

So if I joinining these 3 tables I might end up with for instance:

UserID, Username; RoleName:
1; 'test'; 'Administrators'
1; 'test'; 'Mobile Users'
2; 'test1'; 'Administrators'

As you can see the UserID 1 has 2 roles

I want to display in my grids as follow:

UserID, Username; RoleName:
1; 'test'; 'Administrators, Mobile Users'
2; 'test1'; 'Administrators'

The query that I use is this:

SELECT UserID
Username,
STUFF((SELECT CAST(rl.RoleName AS VARCHAR(64)) AS RoleName
FROM Roles rl
INNER JOIN UsersInRoles uir ON rl.RoleID = uir.RoleID
WHERE UserID = u.UserID
FOR XML PATH('')),1,0,'') AS RoleDescription
FROM Users

The data returned for the RoleDescription is returning in XML format such as:

<RoleDescription> Administrators</RoleDescription><RoleDescription>Mobile Users</RoleDescription>

BUT I have a similar query but more complex like this and it returns what I want ?!?!

Any ideas?

dewacorp.alliances

452 Posts

Posted - 2009-06-20 : 19:40:27

Actually ... I found the solution after comparing with my previous query by removing the RoleName after the CAST one and it works. Data return into: 'Administrators, Mobile Uses' instead of XML format one.

SELECT UserID
Username,
STUFF((SELECT ',' + CAST(rl.RoleName AS VARCHAR(64))
FROM Roles rl
INNER JOIN UsersInRoles uir ON rl.RoleID = uir.RoleID
WHERE UserID = u.UserID
FOR XML PATH('')),1,1,'') AS RoleDescription
FROM Users

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-21 : 00:49:07

hi,
why are u converting the rolename as varchar in the stuff statement
in table definition rolename is varchar field
no need of cast(rl.rolename as varchar(64))

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-21 : 20:22:58

A better question to ask is why is such concatenation required? What are the business requirements that drive this form of denormalization?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources