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 Go to next message
pointers
Messages: 446
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67949
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 Go to previous messageGo to next message
pointers
Messages: 446
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 #654247 is a reply to message #654246] Thu, 28 July 2016 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67949
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/m/653797/?srch=pkg_pivot#msg_653797

Re: place set values next to each other [message #654251 is a reply to message #654247] Thu, 28 July 2016 13:47 Go to previous message
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.
Previous Topic: How to display report format data
Next Topic: Extract value from xml
Goto Forum:
  


Current Time: Fri Sep 24 06:54:37 CDT 2021