SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Pivoting using STUFF and XML PATH
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 06/20/2009 :  19:17:26  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
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?

Edited by - dewacorp.alliances on 06/20/2009 19:20:32

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 06/20/2009 :  19:40:27  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
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

Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 06/21/2009 :  00:49:07  Show Profile  Reply with Quote
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))
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 06/21/2009 :  20:22:58  Show Profile  Reply with Quote
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... http://www.sqlservercentral.com/articles/Best Practices/61537/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000