CODEWITHSUNDEEP
sqldatabaseoracle10g
Jacob
Jacob

Reputation: 14731

SQL results in single row

Below is my table script and records.

create table prods
(prod_no varchar2(12));

insert into prods
values('MOBILE');

insert into prods
values('LAPTOP');

select prod_no from prods; gives me 

MOBILE
LAPTOP.

How can I get the results like the following?

MOBILE LAPTOP. I would like the two records in one single row.

Upvotes: 0

Views: 204

Answers (3)

batressc
batressc

Reputation: 1588

Try this:

--Transact-SQL
--In this case, store in the variable varchar values
declare @result varchar(max) = '';

select @result = @result + prod_no + ' ' from prods;

select @result;

Upvotes: 0

Gaurav Soni
Gaurav Soni

Reputation: 6336

create table prods
(prod_no varchar2(12));

insert into prods
values('MOBILE');

insert into prods
values('LAPTOP');

select 
   rtrim (xmlagg (xmlelement (e, prod_no || ' ')).extract ('//text()'), ' ') list
from 
prods;

LIST                                                                            
---------------------------------------------------------
MOBILE LAPTOP                                                                   
1 row selected.

SQLFIDDLE:link

Upvotes: 2

Kundan Singh Chouhan
Kundan Singh Chouhan

Reputation: 14292

I think you need to pivot the table. Go through the below URL

Visit: http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

Upvotes: 0

Related Questions