Oracle query

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!
ralph_reaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
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
 
ralph_reaAuthor Commented:
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
 
dbmullenConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.