CODEWITHSUNDEEP
databaseoracle-databaseoracle10g
user1630575
user1630575

Reputation: 171

Query to show two columns of single column from table

I need some help with query writing. I have a table as in SQLFiddle:

http://sqlfiddle.com/#!2/7f51a

I need a result set as below, i.e: The query should show three columns, first is name,second is 'One' and third is 'two'. It shows data in col 'one' only if that name has value 'one' in tbl1 else it should stay blank. Similary third column should show value only if 'Name' has corresponding 'two'value.

Name    |   One |   Two
--------+-------+----------
Jig     |   Yes |   Yes  
Mig     |       |   Yes  
Dig     |   Yes |

Upvotes: 0

Views: 81

Answers (3)

San
San

Reputation: 4538

Using Pivot in 11g+ and above:

select *
  from tbl1
  pivot (max('Yes') for book in ('one' as one, 'two' as two) );

output:

| NAME |    ONE |    TWO |
|------|--------|--------|
|  Dig |    Yes | (null) |
|  Jig |    Yes |    Yes |
|  Mig | (null) |    Yes |

Sqlfiddle : http://sqlfiddle.com/#!4/d09cf9/1

Upvotes: 1

redsoxlost
redsoxlost

Reputation: 1235

You can use the Query 

select * from tbl1;



select name
      ,max(decode(book,'one','yes','-')) as one
      ,max(decode(book,'two','yes','-')) as two

      from tbl1

group by name;

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

Try this:

SELECT Name,
      IFNULL(MAX(CASE WHEN Book ='one' THEN 'Yes' END),'') as One,
      IFNULL(MAX(CASE WHEN Book='two' THEN 'Yes' END),'') as Two
FROM tbl1
GROUP BY Name

NB: For oracle, use NVL instead of IFNULL.

Result:

NAME    ONE   TWO
Dig     Yes 
Jig     Yes   Yes
Mig           Yes

Fiddle here.

Upvotes: 1

Related Questions