| using pivot [message #622159] |
Thu, 21 August 2014 16:22  |
ora1980
Messages: 251
Registered: May 2008
|
Senior Member |
|
|
create table mytest( mykey number,
mytype varchar2(10),
key1 number,
mytext varchar2(50));
insert into mytest values (1,'A1',100,'TEXT1');
insert into mytest values (1,'A2',200,'TEXT2');
commit;
I need the result in following:
MYKEY A1_KEY1 A2_KEY1 A1_MYTEXT A2_MYTEXT
1 100 200 TEXT1 TEXT2
I tried the following, but how do I make it work for mytext column too ? also how do I name the new columns ?
select * from ( select key1, MYTYPE from MYTEST
)
pivot
( SUM(KEY1)
for MYTYPE in ('A1','A2')
)
|
|
|
|
| Re: using pivot [message #622161 is a reply to message #622159] |
Thu, 21 August 2014 19:31  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl12c> COLUMN mytext FORMAT a15
SCOTT@orcl12c> SELECT * FROM mytest
2 /
MYKEY MYTYPE KEY1 MYTEXT
---------- ---------- ---------- ---------------
1 A1 100 TEXT1
1 A2 200 TEXT2
2 rows selected.
SCOTT@orcl12c> COLUMN a1_mytext FORMAT A15
SCOTT@orcl12c> COLUMN a2_mytext FORMAT A15
SCOTT@orcl12c> SELECT mykey, a1_key1, a2_key1, a1_mytext, a2_mytext
2 FROM (SELECT mykey, key1, mytext, mytype
3 FROM mytest)
4 PIVOT (SUM (key1) AS key1, MAX (mytext) AS mytext
5 FOR mytype IN ('A1' AS a1, 'A2' AS a2))
6 /
MYKEY A1_KEY1 A2_KEY1 A1_MYTEXT A2_MYTEXT
---------- ---------- ---------- --------------- ---------------
1 100 200 TEXT1 TEXT2
1 row selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
