How 2 get comma seperated values frm multiple rows

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

Transact-SQL (2008)

How 2 get comma seperated values frm multiple rows

Author

Topic

nayanancha
Starting Member

27 Posts

Posted - 2012-02-24 : 09:11:37

I am using the below dynamic sql in my sp. I want the rolename for the user to be comma seperated value.

set @sql =
			'
			 select uc.UserName,
					r.RoleName
			from ' + @PortalDb30 + '.dbo.aspnet_Membership m
					inner join ' + @Portal + '.dbo.UserConfig uc
						on uc.userid = m.userid
					inner join ' + @Portal + '.dbo.Company c
						on c.CompanyCode = uc.CompanyCode
					inner join ' + @Portal + '.dbo.aspnet_UsersInRoles ur
						on ur.userid = uc.userid
					inner join ' + @Portal + '.dbo.aspnet_Roles r
						on r.RoleId = ur.RoleId
			where c.CompanyCode = ''' + @CompanyCode + ''''

		If @UserName is NOT NULL
			set @sql = @sql + ' and uc.UserName = ''' + @UserName + ''' order by uc.UserName '
		Else If @RoleName IS NOT NULL
			set @sql = @sql + ' and r.RoleName = ''' + @RoleName + ''' order by uc.UserName'
		Else
			set @sql = @sql + ' order by uc.UserName'		

		
		exec sp_executesql @sql

I am getting the output as below:

Username Rolename
user1 Admin
user1 Customer

But I want the output as

user1 Admin,Customer

How can I acheive this?

Jayam.cnu
Starting Member

45 Posts

Posted - 2012-02-24 : 10:03:32

can you follow this link.......... http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 10:04:12

see scenario 3

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nayanancha
Starting Member

27 Posts

Posted - 2012-02-24 : 10:27:41

I couldn't follow that scenario. Could you explain it with my code?

Thanks,

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-27 : 17:20:39

My suggestion is to first replace the exec sp_executesql @sql with PRINT @sql, and then use the value of @sql to test with and first get the query working like you want it, then once its working correctly, figure out how you need to modify the dynamic SQL to achieve the same result.

In addition, if you can't figure it out, at least paste in the results of the PRINT statement here so it will be easier to figure out what the query is doing.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-02-27 : 19:25:20

Would this approach work for you?[CODE]declare @tbl table (
pkey int identity(1, 1),
fruit varchar(20) not null
)

insert into @tbl(fruit)
select 'Apples' union all
select 'Bananas' union all
select 'Cherries'

select *
from @tbl

declare @csv_list varchar(max) = ''

update @tbl
set @csv_list += fruit + ','

set @csv_list = LEFT(@csv_list, len(@csv_list) - 1) -- Remove trailing comma

select @csv_list csv[/CODE]It's kind of a wierd use of an UPDATE but I think it gets you what you want.

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources