[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle 10g - Select values as row

Posted on 2012-09-10
7
Medium Priority
?
618 Views
Last Modified: 2012-09-27
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?
0
Comment
Question by:JDCam
  • 6
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38382841
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38382846
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38382858
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:JDCam
ID: 38382889
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38382908
you have to create concat_agg as shown in the article link
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38382914
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38382926
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question