July 25, 2005 at 10:24 pm
G'day all,
I'm looking for a way to return a set of retults on a single line rather than a set of columns.
EG
SELECT egChar + ' 'cast(egInt as char(2)) + ', ' FROM egTable
I want it to look like:
One 1, Two 2, Three 3,.....
I know I can run the query with a counter, adding the next row into a local CHAR variable each time. But I was hoping that SQL might have some built in switch or command existed to cause a select statment to return data in this format.
Am I just dreaming?
Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com
July 26, 2005 at 10:11 am
G'day,
Here is a simple example that shows one method to meet your needs. There are several other ways to do the same thing. Hope this helps
Wayne
create table #x
( id int not null,
name varchar(20),
job varchar(20) not null )
insert #x select 12345, 'Jane Smith', 'Accounts Clerk'
insert #x select 12345, 'Jane Smith', 'Store Assistant'
insert #x select 12345, 'Jane Smith', 'Input Clerk'
declare @job varchar(255)
set @job = ''
select @job = COALESCE (@job + ' - ', '') + job
from #x
PRINT @job
July 27, 2005 at 8:02 am
Give this a try:
declare @list varchar(8000)
set @List = ''
select @List = @List + egChar + ' 'cast(egInt as char(2)) + ', ' FROM egTable
Print @list
I may have messed up the above syntax with lack of apostrophes without testing
.... a query I use to do this is like below:
declare @list varchar(8000)
set @List = ''
select @List = @List + Rtrim(Acct_Nbr) + ''''',''''' from MasterAccount where Acct_State is not null
Print @list
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy