SQL PROBLEM FOR MERGING THE RECORD [message #426180] |
Wed, 14 October 2009 05:34  |
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 #426193 is a reply to message #426180] |
Wed, 14 October 2009 06:08  |
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;
|
|
|