OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » join query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

join query [message #623622]

Fri, 12 September 2014 00:36

Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra

Member

I have one table as mentioned below:

Loc_Cd 
Efe_Dt 
Grade_Cd 
Prize 
Cr_By 
Cr_Dt 
Auth_By 
Auth_Dt 
Flag .

When I Insert data, table will be as below:

Loc_Cd Efe Dt    Grade_Cd Prize  Cr_By Cr_Dt                 Auth_By Auth_Dt Flag 
-----------------------------------------------------------------------------------
1110   12/1/2014 002      100.00 90112 9/12/2014 10:45:45 AM                  C 
1110   12/1/2014 002      200.00 90112 9/12/2014 10:45:45 AM                  R

Now, I have to display data as below:

Grade_cd  C_PRIZE   R_PRIZE
--------------------------------
002          100       200

Please suggest me...

Lalit : Added code tags. In future, please do it yourself. Read How to use [code] tags.

[Updated on: Fri, 12 September 2014 00:38] by Moderator

Report message to a moderator

Re: join query [message #623623 is a reply to message #623622]

Fri, 12 September 2014 00:43

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Please provide a test case, which includes, the create statements, few insert statements as sample data and the expected output.

Report message to a moderator

Re: join query [message #623624 is a reply to message #623622]

Fri, 12 September 2014 00:53

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Now, I have to display data as below:

Standard PIVOT query (search for this word).

Report message to a moderator

Re: join query [message #623625 is a reply to message #623624]

Fri, 12 September 2014 01:05

Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra

Member

In the table name "Transactions_MST" data as below:

Loc_Cd Efe Dt Grade_Cd Prize Cr_By Cr_Dt Auth_By Auth_Dt Flag
-----------------------------------------------------------------------------------
1110 12/1/2014 002 100.00 90112 9/12/2014 10:45:45 AM C
1110 12/1/2014 002 200.00 90112 9/12/2014 10:45:45 AM R
1110 12/1/2014 003 300.00 90112 9/12/2014 11:34:45 AM C
1110 12/1/2014 003 400.00 90112 9/12/2014 11:34:45 AM R
1110 12/1/2014 004 500.00 90112 9/12/2014 11:34:45 AM C
1110 12/1/2014 004 600.00 90112 9/12/2014 11:34:45 AM R

I have to display as like below:

Grade_cd C_PRIZE R_PRIZE
--------------------------------
002 100 200
003 300 400
004 500 600

Report message to a moderator

Re: join query [message #623626 is a reply to message #623625]

Fri, 12 September 2014 01:09

Mayur Shinde
Messages: 60
Registered: August 2014
Location: Pune, Maharashtra

Member

I complete it by using PIVOT, using below query:
SELECT GRADE_CD,
sum(case when FLAG='C' then C_PRIZE else 0 end) C_PRIZE,
sum(case when FLAG='R' then C_PRIZE else 0 end) R_PRIZE
FROM TRANSACTIONS_MST
group by GRADE_CD;

Thank you all...

Report message to a moderator

Re: join query [message #623627 is a reply to message #623626]

Fri, 12 September 2014 01:12

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Since we don't have your table and data, we cannot validate if that's correct. But, that is not PIVOT.

Report message to a moderator

Re: join query [message #623628 is a reply to message #623626]

Fri, 12 September 2014 01:14

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for the feedback and solution.
Note your solution assume there are only 1 C row and 1 R row per GRADE_CD (whatever are the dates and all other columns but flag).

Before you next question, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.

Report message to a moderator

Re: join query [message #623629 is a reply to message #623627]

Fri, 12 September 2014 01:15

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

But, that is not PIVOT.

Yes, it is PIVOT.

Report message to a moderator

Re: join query [message #623631 is a reply to message #623629]

Fri, 12 September 2014 02:44

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Yes, now I see. When I initially saw it in hurry I was looking for PIVOT keyword, without code tags it is difficult to analyze a code quickly.

Report message to a moderator

Previous Topic:	".." Character coming instead of "." while creating an excel spreadsheet from Oracle 10g
Next Topic:	interact command line select to files in this ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 17:54:45 CDT 2024