Solved

Oracle query

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

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

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.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

635 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