CODEWITHSUNDEEP
oracle-database
akhil
akhil

Reputation: 1202

Display single column wise record for multiple row wise records

I am using Oracle database for my task below.

I have a table in which the data is as below - 

 ID      SSN
 -------------
 10       A
 20       A
 10       B
 20       B
 30       B
 20       C

Now, I want to display these records in the below format - 

 SSN      ID_10_Indicator     ID_20_Indicator    ID_30_Indicator
 ----------------------------------------------------------------
 A            Y                      Y                 N
 B            Y                      Y                 Y
 C            N                      Y                 N

I am using below query - 

select ssn, 
(case when ID = '10' then 'Y' else 'N') as ID_10_Indicator, 
(case when ID = '20' then 'Y' else 'N') as ID_20_Indicator,
(case when ID = '30' then 'Y' else 'N') as ID_30_Indicator
from table1
group by ssn, 
(case when ID = '10' then 'Y' else 'N'),
(case when ID = '20' then 'Y' else 'N'),
(case when ID = '30' then 'Y' else 'N')

But I am not getting unique rows for SSN. Instead I am getting records as below - 

 SSN      ID_10_Indicator     ID_20_Indicator    ID_30_Indicator
 ----------------------------------------------------------------
 A            Y                      N                 N     
 A            N                      Y                 N
 B            Y                      N                 N
 B            N                      Y                 N
 B            N                      N                 Y
 C            N                      Y                 N

Please suggest. Any help would be great.

Upvotes: 1

Views: 1311

Answers (1)

schurik
schurik

Reputation: 7928

select ssn, 
  NVL (MAX (CASE ID when '10' then 'Y' else null end ),'N') as ID_10_Indicator, 
  NVL (MAX (CASE ID when '20' then 'Y' else null end ),'N') as ID_20_Indicator, 
  NVL (MAX (CASE ID when '30' then 'Y' else null end ),'N') as ID_30_Indicator
from table1
group by ssn
;

Upvotes: 3

Related Questions