Home » SQL & PL/SQL » SQL & PL/SQL » SQL PROBLEM FOR MERGING THE RECORD
SQL PROBLEM FOR MERGING THE RECORD [message #426180] Wed, 14 October 2009 05:34 Go to next message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

i have table structure like this

Sr_no number(1),
cut varchar2(3),
polish varchar2(3),
symmetry varchar2(3)

Values in this table would be like this

Sr_no Cut Polish Symmetry
1213 VG VG G
1345 VG G EX
4321 EX G VG
1234 EX EX G
4325 VG VG G

DESIRED RESULT :

COUNT OF 2 VG FIELD AND 1 G FIELD ROWS
COUNT OF 1 VG FIELD AND 1 G FIELD AND 1 EX ROWS

HOW TO WRITE THIS QUERY?

Waiting for the reply

Thanking you in advance

Samit Gandhi
Re: SQL PROBLEM FOR MERGING THE RECORD [message #426182 is a reply to message #426180] Wed, 14 October 2009 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous posts:
Michel Cadot wrote on Thu, 10 May 2007 08:13

DON'T SHOUT.

We help you if you shout you'll be on your own.

And post what you tried.
And your Oracle version.
And don't forget to format.
But you know that as you already read the sticky before posting.

Regards
Michel

I now add:

Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), 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.

Regards
Michel
Re: SQL PROBLEM FOR MERGING THE RECORD [message #426184 is a reply to message #426182] Wed, 14 October 2009 05:42 Go to previous messageGo to next message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

Sorry Sir,

My oracle version is 10.1.0. i have tried for the your old post http://www.orafaq.com/forum/t/150757/74621 but it is not satisfy my requirement

SOrry one again

Samit Gandhi
Re: SQL PROBLEM FOR MERGING THE RECORD [message #426189 is a reply to message #426184] Wed, 14 October 2009 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Post a working Test case: create table and insert statements along with the result you want with these data.

Do it.

Regards
Michel
Re: SQL PROBLEM FOR MERGING THE RECORD [message #426193 is a reply to message #426180] Wed, 14 October 2009 06:08 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Next time, please post a CREATE TABLE and some INSERT statements - it makes it much easier for us to help you.

This should point you in the right direction:
with src as (select 1213 sr_no, 'VG' as cut, 'VG' as polish, 'G' as symmetry from dual union all
select 1345, 'VG', 'G', 'EX' from dual union all
select 4321, 'EX', 'G', 'VG' from dual union all
select 1234, 'EX', 'EX', 'G' from dual union all
select 4325, 'VG', 'VG', 'G' from dual)
select sr_no
      ,case when cut      = 'G' then 1 else 0 end 
      +case when polish   = 'G' then 1 else 0 end
      +case when symmetry = 'G' then 1 else 0 end g_cnt
      ,case when cut      = 'VG' then 1 else 0 end 
      +case when polish   = 'VG' then 1 else 0 end
      +case when symmetry = 'VG' then 1 else 0 end vg_cnt
      ,case when cut      = 'EX' then 1 else 0 end 
      +case when polish   = 'EX' then 1 else 0 end
      +case when symmetry = 'EX' then 1 else 0 end ex_cnt
from src;
Previous Topic: function required
Next Topic: UTL_SMTP & Mailing Issue (merged)
Goto Forum:
  


Current Time: Fri Dec 09 13:51:00 CST 2016

Total time taken to generate the page: 0.06562 seconds