Solved

Oracle query

Posted on 2014-03-07
4
316 Views
Last Modified: 2014-03-20
Hi,
I've this query:

select A.NAME, B.VALUE
from MYTABLE A
join SETTING B
on B.T_ID = A.T_ID
join C_INST c
on C.ST_ID = B.st_id
where c.name = 'HP'
order by 1,2;

with this output:

NAME                  VALUE
AS                        11
PROG                  16
RD                        10
RT                      100
TI                        12
TG                        002

I'd like to get this new output:


AS            PROG       RD            RT            RT            TG
11            16            10            100            12            002

How can I rewrite the query to get this output (pivot query)?

Thanks in advance!
0
Comment
Question by:ralph_rea
4 Comments
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 100 total points
ID: 39912266
This can be done with the PIVOT function. Have a look here: http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php
Look for the 3rd code section, this should put you in the position of being able to help yourself ;-) That way, you'll learn something!
This is just one site with references and examples, there are a whole lot more out there ;-)

Good luck & come back if you need further help and/or guidance!

Kind regards,
Alex
0
 

Author Comment

by:ralph_rea
ID: 39912652
I tried this query:

SELECT SUM(CASE WHEN n.NAME = 'AS' THEN s.VALUE ELSE 0 END) AS "AS",
SUM(CASE WHEN n.NAME = 'PROG' THEN s.VALUE ELSE 0 END) AS "PROG",
SUM(CASE WHEN n.NAME = 'RD' THEN s.VALUE ELSE 0 END) AS "RD",
SUM(CASE WHEN n.NAME = 'RT' THEN s.VALUE ELSE 0 END) AS "RT",
SUM(CASE WHEN n.NAME = 'TI' THEN s.VALUE ELSE 0 END) AS "TI",
SUM(CASE WHEN n.NAME = 'TG' THEN s.VALUE ELSE 0 END) AS "TG"
from MYTABLE A
join SETTING B
on B.T_ID = A.T_ID
join C_INST c
on C.ST_ID = B.st_id
where c.name = 'HP'

Open in new window


but NAME and VALUE columns are VARCHAR2(64)

Have someone any idea?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 39913763
Convert the value to a number:  TO_NUMBER(s.VALUE)?

Oracle should do an implicit data conversion for you.

It might be complaining when you return either a string or a number:
THEN s.VALUE ELSE 0 END

I think both values need to be the same data type.  TO_NUMBER is the correct action here but the other way is make the '0' a string like '0'.

If this isn't it, what happens when you run what you posted?
0
 
LVL 10

Assisted Solution

by:dbmullen
dbmullen earned 100 total points
ID: 39932703
assuming oracle 11g and above:
NAME, VALUE, and AS are key words, I put a 1 at the end of them...  
you want a PIVOT

SELECT *
  FROM my_table PIVOT (MAX (value1)
                FOR (name1)
                IN  ('AS' AS AS1,
                    'PROG' AS PROG,
                    'RD' AS RD,
                    'RT' AS RT,
                    'TI' AS TI,
                    'TG' AS TG))

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now