Home » SQL & PL/SQL » SQL & PL/SQL » UNION ALL output in multiple colums
UNION ALL output in multiple colums [message #571461] Mon, 26 November 2012 03:40 Go to next message
manash_baruah
Messages: 1
Registered: November 2012
Junior Member
Following is my table structure:

14:54:17 PYMTPRODIAT@UCS43 > select * from DGTEST;

ID AMOUNT DI
---------- ---------- --
1 50 D
2 50 D
3 20 D
4 60 C
2 60 C
3 20 C

now, I want the output in the following format.what could be the easiest way.. I need the output in one query,,

ID D_Net_Amount C_Net_Amount
--- ------------- --------------
1 50
2 50 60
3 20 20
4 60
Re: UNION ALL output in multiple colums [message #571464 is a reply to message #571461] Mon, 26 November 2012 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Your problem is a standard rows to columns (or pivot) one, search for these words you will find some solutions.

Regards
Michel
Re: UNION ALL output in multiple colums [message #571472 is a reply to message #571464] Mon, 26 November 2012 04:06 Go to previous message
Maaher
Messages: 7041
Registered: December 2001
Senior Member
Follow Michel's advice next time.

Anyway, here's a test case. The setup:
Create Table  DGTEST ( id     number
                     , amount number
                     , di     varchar2(1)
                     )
/


Insert Into dgtest( id, amount, di) values (1, 50, 'D');
Insert Into dgtest( id, amount, di) values (2, 50, 'D');
Insert Into dgtest( id, amount, di) values (3, 20, 'D');
Insert Into dgtest( id, amount, di) values (4, 60, 'C');
Insert Into dgtest( id, amount, di) values (2, 60, 'C');
Insert Into dgtest( id, amount, di) values (3, 20, 'C');

Select *
From   dgtest
/


You can use a Group By solution:
Select id
     , max(decode(di, 'D', amount)) d_amount
     , max(decode(di, 'C', amount)) c_amount
From   dgtest
Group  By id
/


MHE
Previous Topic: to get the results in the form of a tree
Next Topic: Handle customer specific objects in Oracle
Goto Forum:
  


Current Time: Thu Jul 31 00:56:33 CDT 2014

Total time taken to generate the page: 0.12537 seconds