returning columns as delimited string IN a select statement

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

SQL Server Development (2000)

returning columns as delimited string IN a select statement

Author Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-16 : 07:39:54

Emi writes "Hi,

I found a few articles on how to return columns as a delimited string and I have that portion of my question working. I now need to add that string to the output of a select statement. All my attempts thus far have failed. I'm relatively new to sql, but am trying to do this without any temporary or derived tables.

This task is probably a common one. I have three tables:
account_user (id, name)
account_role (id, name)
account_userRoleMap (user_id, role_id)

I am using Sql Server 2000, Service Pack 3 (on Windows 2000 Server SP3) trying to get this to run in Query Analyzer, although eventually it will be called as a stored procedure from an asp.net page.

I would like to return a recordset containing the following:
id(user id), name (user name), roles (; delimited string).

Here's what I have so far:


Declare
@tmp nvarchar(4000),
@roleName nvarchar(50),
@id int

SELECT @id=r.id, r.name, @roleName=r.name, (
	SELECT @tmp = COALESCE(@tmp + ';', '') + CAST(@roleName AS nvarchar(50))
	FROM account_role r
	INNER JOIN account_userRoleMap ur ON r.id = ur.role_id
	WHERE ur.user_id = @id 
	ORDER BY r.name
) SELECT @tmp as roles
FROM account_user

I receive the following error:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '='.

I have also tried the code above in brackets as it's own stored procedure like so:


DECLARE
@tmp nvarchar(4000),
@roleName nvarchar(50),
@id int

SELECT @id=r.id, r.name, r.name, 
(EXEC core_GetRolesByUserId @id) as roles
FROM account_user

I received more errors this way, especially with the 'EXEC'. What am I doing wrong?

Thanks so much!

Emi ^_^"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-16 : 07:40:48

quote:
but am trying to do this without any temporary or derived tables.

What's wrong with temp tables and derived tables?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-16 : 09:28:59

Read byrmol's post and look at his solution using a UDF:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647

It is a really great way to do this, and very efficient, and can be used in a regular old select statement. It just isn't dynamic; you need 1 function per table that needs this functionality. But it really is the best way to do this sort of thing in my opinion.

- Jeff

skillile
Posting Yak Master

208 Posts

Posted - 2003-04-16 : 23:21:32

If you already have your user and role tables this should do.

CREATE PROC ps_xxx

(
@uroles varchar(100) out
)

AS

/* need the temp tables for demo you have them as real already */

DECLARE @uid TABLE (
uid bigint
)

DECLARE @uid_role TABLE (
uid bigint,
roleid int
)

INSERT @uid (uid)
SELECT 100
UNION ALL
SELECT 200

INSERT @uid_role(uid, roleid)
SELECT 100, 5
UNION ALL
SELECT 100, 10
UNION ALL
SELECT 100, 15

--YOU HAVE ABOVE ALREADY START NEXT LINE

SET @uroles = ''

SELECT
@uroles = @uroles + CAST(b.roleid as varchar(5) ) + ', '

FROM @uid a
INNER JOIN @uid_role b ON (a.uid = b.uid)

--JUST TO SHOW YOU THE RESULT IT IS OUTPUTED AS VARIABLE
SELECT @uroles

slow down to move faster...

skillile
Posting Yak Master

208 Posts

Posted - 2003-04-16 : 23:24:34

BTW don't use name as a field it is a SQL SRV reserved keyword
if you do use it then [name] that may be your error.

Sorry, if you want a RS

<I would like to return a recordset containing the following:
id(user id), name (user name), roles (; delimited string). >

SELECT
id,
[name],
@uroles

FROM table
WHERE uid = @uid

blah blah

just don't use the output var and place into rs

slow down to move faster...

Edited by - skillile on 04/16/2003 23:28:21

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources