Concatenated string - SQL Server Forums

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)

Concatenated string

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2007-01-31 : 14:53:18

Can someone tell me how to create a variable and set it's value to a concatenated string whos items are returned by a select statement. For example. I have the following Table 'Users'

Field1: User.. contains U1, U2, U3
Fiels2: Name.. contains John, Mark, Paul

If I have a select statement that returns the data I need, like "SELECT User, Name FROM Users"

I would like to create a string:

DECLARE @str varchar(max)

SET @str = ..... and have it set to 'U1|U2|U3'

This variable will be passed to another stored procedure as an input parameter. I also need to be able to specify different delimiters ("|" in my example case).

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 14:58:08

See this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78378

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2007-01-31 : 15:20:46

That topic claims that info on the dbo.fnConcatString function can be found on SQL Team, but I searched and it is nowhere to be found.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-31 : 15:40:29

[code]CREATE TABLE Users
([User] varchar(10),
[Name] varchar(50))

INSERT Users
SELECT 'U1', 'John' UNION ALL
SELECT 'U2', 'Mark' UNION ALL
SELECT 'U3', 'Paul'

DECLARE @str varchar(max)
DECLARE @delimitter char(1)
SET @delimitter = '|'
SELECT @str = coalesce(@str + @delimitter + [User], [User]) FROM Users
SELECT @str
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 15:46:00

Similar to this

create function dbo,fnConcatString
(
	@id int
)
returns varchar(8000)
AS
begin
	declare	@csv varchar(8000)

	select	@csv = coalesce(@csv + '|', '') + d.[user]
	from	(
			SELECT distinct	top 100 percent [user]
			from		users
			where		id = @id
			order by	[user]
		) AS d

	return	@csv
end

select distinct name, dbo.fnconcatstring(name) from users

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-31 : 16:11:02

Calling it fnConcatString is perhaps a little misleading because it suggests that it is a generic function to concatenate a string for any column in any table, whereas it is actually specifically created to work with the User column in the Users table. Maybe something like fnConcat_Users_User?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 16:19:27

It's your database. You decide...

Peter Larsson
Helsingborg, Sweden

akashenk
Posting Yak Master

111 Posts

Posted - 2007-01-31 : 17:09:42

I agree with both Peso and snSQL :)

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources