Oracle 10g - Select values as row

Experts,

Consider this Simple table:

REC      TYPE      VALUE
100      AA      AAVALUE
100      BB      BBVALUE
100      CC      CCVALUE
100      DD      DDVALUE
100      EE      EEVALUE

I want to select and return 3 values as a single row (not a list).  
Where TYPE in (‘AA’, ‘CC’, ‘DD’)
AND REC = ‘100’

How do I accomplish this?
LVL 1
JDCamAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
http://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html


select concat_agg(value) from yourtable
Where TYPE in (‘AA’, ‘CC’, ‘DD’) AND REC = ‘100’
0
 
sdstuberCommented:
or, without creating your own aggregate

select RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", value || ',')), '/x/text()').getstringval(),',')
from yourtable
Where TYPE in (‘AA’, ‘CC’, ‘DD’) AND REC = ‘100’
0
 
sdstuberCommented:
if you upgrade to 11gr2 you can use the built-in function listagg

some people might suggest using wm_concat - but that is undocumented and not a supported option from Oracle
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
JDCamAuthor Commented:
sdstuber... excelent as always.

CONCAT_AGG is 'invalid identifier'

I see the word CONCAT and worry maybe I wan't clear.
I need each value in its own column, but within a single row.

AA               CC             DD
AAVALUE    CCVALUE    DDVALUE
0
 
sdstuberCommented:
you have to create concat_agg as shown in the article link
0
 
sdstuberCommented:
but... if you want them as separate columns, that's a pivot not an concatenation

you can't have dynamic columns.  That is, all of the columns must be known at the time the sql statement is parsed, before it runs - not after it has finished executing.
0
 
sdstuberCommented:
11g introduces the PIVOT command to turn rows into columns, but it too requires them to be defined at parse time.

in 10g you can simulate PIVOT with conditional aggregation like this...

S ELECT MAX(CASE WHEN TYPE = 'AA' THEN VALUE END) aa,
       MAX(CASE WHEN TYPE = 'BB' THEN VALUE END) bb,
       MAX(CASE WHEN TYPE = 'CC' THEN VALUE END) cc
  FROM yourtable
 WHERE TYPE IN ('AA', 'BB', 'CC') AND rec = 100
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.