Return multiple rows/lines on a single row/line

• Charles Wilkinson

  Old Hand

  Points: 323

  More actions

  July 25, 2005 at 10:24 pm

  #165088

  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?

   

  ---

  Kindest Regards,

  Charles Wilkinson
  Database Administrator
  Sastek Pty Ltd
  www.sastek.com

• Wayne Lawton

  Hall of Fame

  Points: 3532

  More actions

  July 26, 2005 at 10:11 am

  #577226

  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

   

• Steve Fine

  Ten Centuries

  Points: 1164

  More actions

  July 27, 2005 at 8:02 am

  #577550

  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