Solved

Oracle query

Posted on 2014-03-07
4
318 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 2 46
data lookup in Oracle - need suggestions 55 104
Delphi Ado Query To Converts Joined Columns Into Rows Based on Primary Key 4 41
selective queries 7 22
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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, 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.

862 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

23 Experts available now in Live!

Get 1:1 Help Now