May 20, 2008 at 12:08 pm
/*
I'm writing a query for a report and I need to combine multiple values into a a single field for output
The actual query is more complicated than below, but I created the example below as an example
I'm working on SQL Server 2005
Any other solutions?
Thanks
*/
declare @table table(identifier int identity(1,1), ModuleID tinyint)
insert into @table (ModuleID) values(1)
insert into @table (ModuleID) values(2)
insert into @table (ModuleID) values(3)
declare @counter tinyint, @ModuleID tinyint, @string varchar(10), @maxcounter tinyint
set @counter = 1
set @string = ''
set @maxcounter = (select max(identifier) from @table)
while @counter <= @maxcounter
begin
set @string = @string + (select convert(char(1),ModuleID) from @table where identifier = @counter) + ','
set @counter = @counter + 1
end
--remove last comma
if(len(@string)>0)
select @string = substring(@string,1,len(@string)-1)
select @string
May 20, 2008 at 12:25 pm
As long as you're selecting just that one column to concat, there's a trick that I believe is specific to SQL Server:
declare @table table(identifier int identity(1,1), ModuleID tinyint)
insert into @table (ModuleID) values(1)
insert into @table (ModuleID) values(2)
insert into @table (ModuleID) values(3)
declare@string varchar(10)
set @string = ''
SELECT@string = @string + Cast(ModuleID as varchar(1)) + ','
FROM@table
SELECT Left(@string,Len(@string)-1)
There's better ways of evaluating the end of the string for that last comma, of course.
--SJT--
May 20, 2008 at 12:30 pm
Sauron J. Terrill (5/20/2008)
As long as you're selecting just that one column to concat, there's a trick that I believe is specific to SQL Server:
declare @table table(identifier int identity(1,1), ModuleID tinyint)
insert into @table (ModuleID) values(1)
insert into @table (ModuleID) values(2)
insert into @table (ModuleID) values(3)
declare@string varchar(10)
set @string = ''
SELECT@string = @string + Cast(ModuleID as varchar(1)) + ','
FROM@table
SELECT Left(@string,Len(@string)-1)
There's better ways of evaluating the end of the string for that last comma, of course.
--SJT--
Thanks, that's pretty slick. How would you delete the last comma?
May 20, 2008 at 12:34 pm
If you build the string this way, you don't even have to remove a final comma.
declare @String varchar(max)
select @string = coalesce(@string + ',' + cast(ModuleID as varchar(10)),
cast(ModuleID as varchar(10)),
@string)
from dbo.Table
This version can also deal with null values in the field being concatenated (which may not matter, but sometimes does).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 12:38 pm
As an aside, I'd not use Varchar(1) for this. Sure, it works for single-digit numbers, but it won't work for anything bigger. And, if you know before-hand that it'll never be more than single-digit, use Char(1) instead. Uses less RAM and CPU (by a tiny amount, but it's a good habit to be in).
Varchar(x) uses 2 more bytes of storage/RAM than Char(x).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 12:44 pm
GSquared (5/20/2008)
If you build the string this way, you don't even have to remove a final comma.
declare @String varchar(max)
select @string = coalesce(@string + ',' + cast(ModuleID as varchar(10)),
cast(ModuleID as varchar(10)),
@string)
from dbo.Table
This version can also deal with null values in the field being concatenated (which may not matter, but sometimes does).
I'm getting errors when I run this.
May 20, 2008 at 12:56 pm
Agreed on all points, GSquared. Thanks for putting in the effort to clarify.
May 20, 2008 at 2:00 pm
lfmn (5/20/2008)
GSquared (5/20/2008)
If you build the string this way, you don't even have to remove a final comma.
declare @String varchar(max)
select @string = coalesce(@string + ',' + cast(ModuleID as varchar(10)),
cast(ModuleID as varchar(10)),
@string)
from dbo.Table
This version can also deal with null values in the field being concatenated (which may not matter, but sometimes does).
I'm getting errors when I run this.
What error? Can you copy-and-paste the error message into the forum?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 5:20 am
GSquared (5/20/2008)
lfmn (5/20/2008)
GSquared (5/20/2008)
If you build the string this way, you don't even have to remove a final comma.
declare @String varchar(max)
select @string = coalesce(@string + ',' + cast(ModuleID as varchar(10)),
cast(ModuleID as varchar(10)),
@string)
from dbo.Table
This version can also deal with null values in the field being concatenated (which may not matter, but sometimes does).
I'm getting errors when I run this.
What error? Can you copy-and-paste the error message into the forum?
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'Table'.
May 21, 2008 at 7:33 am
GSquared meant to put @table...
Syntax:
declare @table table(identifier int identity(1,1), ModuleID tinyint)
insert into @table (ModuleID) values(1)
insert into @table (ModuleID) values(2)
insert into @table (ModuleID) values(3)
declare @counter tinyint, @ModuleID tinyint, @string varchar(max), @maxcounter tinyint
select @string = coalesce(@string + ',' + cast(ModuleID as varchar(10)),
cast(ModuleID as varchar(10)),
@string)
from @table
select @string
Rgs
Phil
May 21, 2008 at 9:32 am
On the syntax error, I can't duplicate it. I tested the code, and it compiled and ran just fine. Did you copy-and-paste the code from this forum into your proc, or did you type it in?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 9:33 am
pduplessis (5/21/2008)
GSquared meant to put @table...Syntax:
declare @table table(identifier int identity(1,1), ModuleID tinyint)
insert into @table (ModuleID) values(1)
insert into @table (ModuleID) values(2)
insert into @table (ModuleID) values(3)
declare @counter tinyint, @ModuleID tinyint, @string varchar(max), @maxcounter tinyint
select @string = coalesce(@string + ',' + cast(ModuleID as varchar(10)),
cast(ModuleID as varchar(10)),
@string)
from @table
select @string
Rgs
Phil
Actually, since the table variable is obviously generic, I put a generic table name. I'm assuming that "@table" or "dbo.Table" will be replaced in the actual code with a real table name (table variable, temp table or permanent table).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply