Home » SQL & PL/SQL » SQL & PL/SQL » place set values next to each other (Oracle 11.2.0.3)
place set values next to each other [message #654234] |
Thu, 28 July 2016 09:35 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have a table data as below.
create table t_spp (sid varchar2(2), col_name varchar2(2), expected number, actual number);
BEGIN
insert into t_spp values ('S1','A', 1,2);
insert into t_spp values ('S1','B', 1,3);
insert into t_spp values ('S1','C', 1,4);
--
insert into t_spp values ('S2','A', 10,11);
insert into t_spp values ('S2','B', 10,12);
insert into t_spp values ('S2','C', 10,12);
--
insert into t_spp values ('S3','A', 100,20);
insert into t_spp values ('S3','B', 99, 40);
insert into t_spp values ('S3','C', 98, 50);
END;
/
SELECT * FROM T_SPP;
-- set 1
S1 A 1 2
S1 B 1 3
S1 C 1 4
-- set 2
S2 A 10 11
S2 B 10 12
S2 C 10 12
-- set 3
S3 A 100 20
S3 B 99 40
S3 C 98 50
Each set will have same column_name values (i.e. A, B, C).
I would like to get the output as below.
column_name expected actual expected actual expected actual
A 1 2 10 11 100 20
B 1 3 10 12 99 40
C 1 4 10 12 98 50
I would like to place each set expected column and actual column values next to each other
Thank you in advance.
Regards,
Pointers
|
|
|
Re: place set values next to each other [message #654235 is a reply to message #654234] |
Thu, 28 July 2016 11:22 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Standard pivot query.
Old way:
SQL> with
2 data as (
3 select t.*, row_number() over (partition by col_name order by sid) rn
4 from t_spp t
5 )
6 select col_name,
7 max(decode(rn, 1, expected)) expected,
8 max(decode(rn, 1, actual)) actual,
9 max(decode(rn, 2, expected)) expected,
10 max(decode(rn, 2, actual)) actual,
11 max(decode(rn, 3, expected)) expected,
12 max(decode(rn, 3, actual)) actual
13 from data
14 group by col_name
15 order by col_name
16 /
CO EXPECTED ACTUAL EXPECTED ACTUAL EXPECTED ACTUAL
-- ---------- ---------- ---------- ---------- ---------- ----------
A 1 2 10 11 100 20
B 1 3 10 12 99 40
C 1 4 10 12 98 50
Now try to do it using PIVOT clause.
|
|
|
Re: place set values next to each other [message #654246 is a reply to message #654235] |
Thu, 28 July 2016 13:24 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Thank you very much Micheal for your kind response.
The only issue i see is I've almost 200 or so columns, the count varies ( above was just an example with 3 column values i.e A B C).
Is it possible to do it without actually hard coding.
Thank you in advance.
Regards,
pointers
|
|
|
|
Re: place set values next to each other [message #654251 is a reply to message #654247] |
Thu, 28 July 2016 13:47 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>The only issue i see is I've almost 200 or so columns
The only issue is the the data does NOT conform to Third Normal Form.
Column names should NOT contain application data.
post CREATE TABLE statement for this table.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:23:50 CDT 2024
|