OraFAQ Forum: SQL & PL/SQL » narration in one line

Home » SQL & PL/SQL » SQL & PL/SQL » narration in one line

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

narration in one line [message #649618]

Fri, 01 April 2016 03:59

shahzad-ul-hasan
Messages: 615
Registered: August 2002

Senior Member

Item_id     Item_name              Qty
1           Ghee Supereme(1x12)    23
2           Ghee Medium(0.5x12)    12
3           Oil 1x12)              47

i want to display aobve three items in narration only in one line:

   Narration
   Ghee Supereme (23),Ghee Medium (12),Oil (47)

Report message to a moderator

Re: narration in one line [message #649619 is a reply to message #649618]

Fri, 01 April 2016 04:05

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Take it slowly. First, You seem to be applying some sort of substringing algorithm to your item_name column. What is it?

Report message to a moderator

Re: narration in one line [message #649620 is a reply to message #649619]

Fri, 01 April 2016 04:09

shahzad-ul-hasan
Messages: 615
Registered: August 2002

Senior Member

Table Name: Item

Item_id    Number(3)    Not Null                 -- Primary key
Item_name  Varchar2(20)                          -- Description of Item
Qty        Number(3)                             -- balance

Report message to a moderator

Re: narration in one line [message #649621 is a reply to message #649620]

Fri, 01 April 2016 04:11

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

What is that meant to mean? If you want to provide a test case (as you should) you need to give a CREATE TABLE statement and three INSERT statements. And to repeat, what is your rule for adjusting the item_name column? How do you know which characters to remove?

[Updated on: Fri, 01 April 2016 04:11]

Report message to a moderator

Re: narration in one line [message #649622 is a reply to message #649618]

Fri, 01 April 2016 04:13

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Once more:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: narration in one line [message #649635 is a reply to message #649622]

Fri, 01 April 2016 09:39

Bill B
Messages: 1971
Registered: December 2004

Senior Member

What you want is easy to do, but doing is is different depending on what your oracle database is. Please type

select * from v$version;

and paste what is returned in this issue.

Report message to a moderator

Re: narration in one line [message #649636 is a reply to message #649618]

Fri, 01 April 2016 09:57

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select * from item order by item_id;
   ITEM_ID ITEM_NAME                   QTY
---------- -------------------- ----------
         1 Ghee Supereme(1x12)          23
         2 Ghee Medium(0.5x12)          12
         3 Oil(1x12)                    47

3 rows selected.

SQL> select listagg(regexp_replace(item_name,'^(.*) *\(.*$','\1')||' ('||qty||')',',')
  2           within group (order by item_id) res
  3  from item
  4  /
RES
--------------------------------------------------------------------------------------
Ghee Supereme (23),Ghee Medium (12),Oil (47)

1 row selected.

Report message to a moderator

Re: narration in one line [message #649637 is a reply to message #649636]

Fri, 01 April 2016 10:28

Bill B
Messages: 1971
Registered: December 2004

Senior Member

And if your not comfortable with regular expression, you can do

select listagg(substr(item_name,1,instr(item_name,'('))||qty||')',',')
        within group (order by item_id) res
from item;

Report message to a moderator

Re: narration in one line [message #649640 is a reply to message #649637]

Fri, 01 April 2016 11:38

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, that's better, just a space is missing to have exactly the same output.

select listagg(substr(item_name,1,instr(item_name,'(')-1)||' ('||qty||')',',')
        within group (order by item_id) res
from item;

Report message to a moderator

Re: narration in one line [message #650418 is a reply to message #649640]

Sun, 24 April 2016 22:53

per0per0
Messages: 2
Registered: April 2016

Junior Member

Hi ,

I would like the output to display as

Ghee Supereme (23),Ghee Medium (12) AND Oil (47)

having "," and the "AND" for the separator, AND for the data in the last row. Is it possible with listagg ??

Thank you !

sorry for tagging onto this post .

[Updated on: Sun, 24 April 2016 22:55]

Report message to a moderator

Re: narration in one line [message #650420 is a reply to message #650418]

Mon, 25 April 2016 00:06

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl> -- data:
SCOTT@orcl> SELECT * FROM item ORDER BY item_id
  2  /

   ITEM_ID ITEM_NAME                   QTY
---------- -------------------- ----------
         1 Ghee Supreme(1x12)           23
         2 Ghee Medium(0.5x12)          12
         3 Oil(1x12)                    47

3 rows selected.

SCOTT@orcl> -- original requirements:
SCOTT@orcl> SELECT LISTAGG
  2  	      (SUBSTR (item_name, 1, INSTR (item_name, '(') - 1) || ' (' || qty || ')', ',')
  3  		 WITHIN GROUP (ORDER BY item_id) AS narration
  4  FROM   item
  5  /

NARRATION
--------------------------------------------------------------------------------
Ghee Supreme (23),Ghee Medium (12),Oil (47)

1 row selected.

SCOTT@orcl> -- modified requirements:
SCOTT@orcl> SELECT SUBSTR (narration, 1, INSTR (narration, ',', -1, 1) - 1)
  2  	    || ' AND '
  3  	    || SUBSTR (narration, INSTR (narration, ',', -1, 1) + 1)
  4  	    AS narration
  5  FROM   (SELECT LISTAGG
  6  		      (SUBSTR (item_name, 1, INSTR (item_name, '(') - 1) || ' (' || qty || ')', ',')
  7  			 WITHIN GROUP (ORDER BY item_id) AS narration
  8  	     FROM   item)
  9  /

NARRATION
--------------------------------------------------------------------------------
Ghee Supreme (23),Ghee Medium (12) AND Oil (47)

1 row selected.

Report message to a moderator

Re: narration in one line [message #650461 is a reply to message #650420]

Mon, 25 April 2016 16:07

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

If your DB doesn't support listagg (versions before 11g):

SQL> select * from t_s;
 
     IT_ID IT_NM        QTY
---------- ----- ----------
         1 gs            23
         2 gm            12
         3 oil           47
 
SQL> 
SQL> with t1 as (select it_id,it_nm,qty from t_s where it_id=1),
  2  t2 as(select it_id,it_nm,qty from t_s where it_id=2),
  3  t3 as (select it_id,it_nm,qty from t_s where it_id=3)
  4  SELECT SUBSTR (narration, 1, INSTR (narration, ',', -1, 1) - 1)
  5     	    || ' AND '
  6     	    || SUBSTR (narration, INSTR (narration, ',', -1, 1) + 1)
  7     	    AS narration
  8  from(
  9  select t1.it_nm||'('||t1.qty||'),'||t2.it_nm||'('||t1.qty||'),'||t3.it_nm||'('||t1.qty||')' as narration
 10   from t1,t2,t3)
 11  /
 
NARRATION
--------------------------------------------------------------------------------
gs(23),gm(23) AND oil(23)

Report message to a moderator

Re: narration in one line [message #650465 is a reply to message #650461]

Tue, 26 April 2016 01:50

per0per0
Messages: 2
Registered: April 2016

Junior Member

I gt the output i wanted ! Thanks all Smile

))

Report message to a moderator

Previous Topic:	get row counts daily
Next Topic:	Joining four tables..need help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 22:29:39 CDT 2024