asked on

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!

SOLUTION

Alex [***Alex140181***]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralph_rea

ASKER

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?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dbmullen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial