query to output result horizontally

From: songyu <songyu21_at_gmail.com>
Date: Fri, 13 Mar 2009 08:34:44 -0700 (PDT)
Message-ID: <1c1eade5-9fe0-4616-8b7d-2b52cb09dc84_at_v15g2000yqn.googlegroups.com>



I could not figure out a query to display the output below. I'm running 10g.

Table record


GRP	NAME	XTRANS	QUANTITY
1	AAA	    CX	      10
1 	AAA	    YP	      20
1	BBB         CX	     100
1	BBB         YP	     200
2 	AAA	    CX	      80
2	AAA	    YP	      81
2 	BBB	    CX	      90
2	BBB	    YP	      91
3 	AAA	    CX	     123
3	AAA	    YP	     124
3 	BBB	    CX	    1000
3	BBB	    YP	    2000


OUTPUT


	   AAA		   BBB
	CX	YP	CX	YP
1	10	20	100	200
2       80      81	 90      91
3      123     124     1000    2000


I can have multiple groups available say 1-10 but only two disinct names (AAA, BBB) and two distinct XTRANS (CX,YP).

Testcase



create table testtable (grp number, name varchar2(3), xtrans varchar2 (2), quantity number);
insert into testtable values (1,'AAA','CX',10);
insert into testtable values (1,'AAA','YP',20);
insert into testtable values (1,'BBB','CX',100);
insert into testtable values (1,'BBB','YP',200);
insert into testtable values (2,'AAA','CX',80);
insert into testtable values (2,'AAA','YP',81);
insert into testtable values (2,'BBB','CX',90);
insert into testtable values (2,'BBB','YP',91);
insert into testtable values (3,'AAA','CX',123);
insert into testtable values (3,'AAA','YP',124);
insert into testtable values (3,'BBB','CX',1000);
insert into testtable values (3,'BBB','YP',2000);
commit;

thanks in advance. Received on Fri Mar 13 2009 - 10:34:44 CDT

Original text of this message