Oracle TRICk Question Post: 302743307

Sponsored Content

Top Forums Programming Oracle TRICk Question Post 302743307 by Yoda on Wednesday 12th of December 2012 12:24:09 PM

12-12-2012

Moderator

Use the xmlagg function to aggregate multiple rows onto one column:

Code:

SQL> select '(STATE=' || regexp_replace(state,'([[:alpha:]]{2})',q'['\1']') || ' AND 
ITEMS in (' || regexp_replace(rtrim (xmlagg (xmlelement (e, items || ',')).extract ('//text()'), ','),'([[:alnum:]]{3,4})',q'['\1']') || ')' 
from tmp_state_items group by state;

----------------------------------------------------------------
(STATE='DF' AND ITEMS in ('2345','ggdh','k5tg','hFF','dgyt')
(STATE='PQ' AND ITEMS in ('ABF','1234','ghyt','DDD','GHTD')

Last edited by Yoda; 12-12-2012 at 01:37 PM.. Reason: Modified regex

Yoda

View Public Profile for Yoda

Visit Yoda's homepage!

Find all posts by Yoda

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Stupid find trick

At work, we use a software development product (from a company that will remain nameless, but whose name may be considered a synonym for "logical"). The development trees are organized beneath a top directory, let's call it "$rat". The first level under $rat contains the major system names, and...

2. UNIX for Dummies Questions & Answers

Looking for tips ant trick extend vi

Hello im looking for sites or tutorials how to extend vi (not vim) for programming and scripting beyond simple editing . Thanks allot

3. Shell Programming and Scripting

Any trick to speed up script?

Hi Guys, I have a script that I am using to convert some text files to xls files. I create multiple temp. files in the process of conversion. Other than reducing the temp. files, are there any general tricks to help speed up the script? I am running it in the bash shell. Thanks.

4. UNIX and Linux Applications

Oracle DB Question

Hi, Where do i post Oracle DB related questions?? I basically have 2 questions - 1. What is pmon and smon? Difference between them? If either or both process fail then how can we monitor them? 2. Is lsnrctl same as tnslsnr? if i want to check for listener status then how would i do...

5. UNIX for Dummies Questions & Answers

A trick to avoid ESC in vim

Hello, I find a trick to avoid pressing ESC without key-maping in vim. I am pleasure using this method, because ALT key is very comfortble for thumb to press. What's the trick? the ALT key. When you are in INSERT mod, press ALT+l switch to COMMAND mod without...

6. UNIX for Dummies Questions & Answers

alternative to the grep trick

Hi, We used to use the below commands often. ps -ef|grep bc ps -ef|grep abc|grep -v grep Both fairly returns the same result. For example, the process name is dynamic and we are having the process name in a variable, how we can apply the above trick. For example "a" is the...

7. UNIX for Dummies Questions & Answers

Vi search starting from line n --- any trick for this ???

Hi all, It does not seem to be possible to use vi to search from text from line n forward or backward or is there a not well known vi trick to do that? What I am trying to do is for example I am on line 100 and I want to search from line 100 backward or forward for a specific text. Using /...

8. UNIX for Beginners Questions & Answers

UNIX trick or command

Hi, Is there any command to do this -- Input is -- Ant Bat Cat Dog Output is -- A_Ant B_Ant A_Bat B_Bat A_Cat B_Cat A_Dog B_Dog

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Stupid find trick

Discussion started by: criglerj

2. UNIX for Dummies Questions & Answers

Looking for tips ant trick extend vi

Discussion started by: umen

3. Shell Programming and Scripting

Any trick to speed up script?

Discussion started by: npatwardhan

4. UNIX and Linux Applications

Oracle DB Question

Discussion started by: Mr. Zer0

5. UNIX for Dummies Questions & Answers

A trick to avoid ESC in vim

Discussion started by: vistastar

6. UNIX for Dummies Questions & Answers

alternative to the grep trick

Discussion started by: pandeesh

7. UNIX for Dummies Questions & Answers

Vi search starting from line n --- any trick for this ???

Discussion started by: newbie_01

8. UNIX for Beginners Questions & Answers

UNIX trick or command

Discussion started by: Indra2011