Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle query

Posted on 2014-03-07
4
Medium Priority
?
329 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 400 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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 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 400 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

688 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