June 22, 2010 at 2:20 pm
This is a sample of a stored procedure I have:
(select users.fullname + ', ' as [text()]
from users, programmer
where d.project_brief_id = programmer.projectbriefid and users.user_id = programmer.userid
for xml path(''))u(userlist)
The code sample works fine.
When I tried to add a union to it, I keep getting an error. Here is a sample I tried:
(select users.fullname + ', ' as [text()]
from users, programmer
where d.project_brief_id = programmer.projectbriefid and users.user_id = programmer.userid
union
select users.fullname + ', ' as [text()]
from users, designer
where d.project_brief_id = designer.projectbriefid and users.user_id = designer.userid
for xml path(''))u(userlist)
Please let me know if you can help me out. Thanks!
June 22, 2010 at 6:58 pm
You will need to union your two sets inside of a subquery and apply the FOR XML outside the subquery. That will get you your desired result, but what exactly are you trying to do? Do you ultimately want to end up with XML or do you ultimately want to end up with a comma delimited list? (I'm curious about the comma being appended to the value.)
SQL guy and Houston Magician
June 22, 2010 at 8:32 pm
I agree with Robert, use a derived table to combine the names before applying FOR XML PATH.. If you want the column to have an alias, you need to use another select..
The structure would be like this:
select * from
(
select * from
(
select users.fullname + ', ' as [text()]
from users, programmer
where d.project_brief_id = programmer.projectbriefid and users.user_id = programmer.userid
union
select users.fullname + ', ' as [text()]
from users, designer
where d.project_brief_id = designer.projectbriefid and users.user_id = designer.userid
) temp
for xml path('')
) u(userlist)
NOTE that there is no table with an alias name of "d" here.. 🙂
I hope this helps..
June 23, 2010 at 8:35 am
@shield_21 - Your solution solved my problem! Thanks a bunch!
@robert Cary - I'm trying to return a comma delimited list. My query is for a project application where i can have multiple programmers working on a project. I need the results of my query to be something like this: project number, project name, and a list of the programmers. I had the programmers listed in one table and the designers listed in another table. I wanted a query that would be able to pull from both tables.
June 23, 2010 at 8:42 am
I thought that might be where you were going with this. You may find this a more straightforward technique
DECLARE @Users VARCHAR(MAX)
select @Users = COALESCE(@Users + ', ') + fullname
FROM (
SELECT users.fullname
from users, programmer
where d.project_brief_id = programmer.projectbriefid and users.user_id = programmer.userid
UNION
select users.fullname
from users, designer
where d.project_brief_id = designer.projectbriefid and users.user_id = designer.userid
) userlist
SQL guy and Houston Magician
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply