June 2, 2005 at 10:55 am
Hi all,
Need tip how to concatenate char values in one string (without cursor)? I mean, there is a table with name (char(50)) and status. I would like to join all names having predefined status in 1 string ((with any separator).
Thanks
June 2, 2005 at 11:23 am
SELECT ISNULL(Field1, '') + ISNULL(Field2, '') + ISNULL(Field3, '') ??
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 2, 2005 at 11:32 am
Can you post the table definition, some sample data along with the expected results so we can easier help you (in case AJ's solution doesn't work in your situation)?
June 2, 2005 at 12:27 pm
Remi, you are right- AJ's solution doesn't work as Field name for all names are the same. For example, table tblTest(name varchar(50), status varchar(20)) and I need to combine all names that have status 'Standard1' in 1 string, i.e. result I am expecting is 'Remi' + ' ' + 'Yuri' + ' ' + 'Bob', etc (in case if name field was numeric I could use SUM for this purpose). Thanks
June 2, 2005 at 12:38 pm
Something like this?`
use northwind
declare @stmt varchar(8000)
select @stmt=isnull(@stmt+', ','')+customerid
from orders
where employeeid=3
select @stmt
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 2, 2005 at 12:48 pm
Frank, thanks a lot!
It works great, but to be honest I can't understand the trick (I mean logic).
June 2, 2005 at 12:50 pm
I'm surprised that he didn't post this link as he usually does.. but this will tell you what you're missing :
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
June 2, 2005 at 12:51 pm
I see now, looks like his site is having problems at the moment...
June 2, 2005 at 1:04 pm
Here's basically how that article concludes :
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects')) as [Columns]
Go
DROP FUNCTION ListTableColumns
GO
Basically what happens is that sql server generates the result set for the query but with a twist... instead of creating a resultset, it assign each new row to the variable. But since the variable is updated after each row, each column name gets concatenated one after the other. That's where the magic happens.
June 2, 2005 at 1:24 pm
Thanks a lot, Remi!
June 2, 2005 at 1:32 pm
Aggregate concatenation is very SQL Server specific and can cause unexpected results in certain cases. http://support.microsoft.com/default.aspx?scid=kb;EN-US;287515
The link to SQL Server MVP Adam Machanic's method was not needed here. This task here is simpler.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 2, 2005 at 1:55 pm
Maybe but in my case, I always preffer to learn more than less.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply