-1

I have a table called Address which has following data

Name  |   Address Line 1   | Type

 Ronn   |   123Street1 NC   |     Primary

 Ronn  |    123XYZ Rd NC | Office

Now, I am creating a report XYZ which has two columns. Name, Primary Address and Office Address.

I want-

Name |  Primary Address   |  Work Address

Ronn |     123Street1 NC   |   123XYZ Rd NC

How do I fetch one row into one column of XYZ and another row into another column of XYZ?

I tried using decode, but it return multiple rows, which is not correct.

I hope I am making sense..it was so difficult to post this thing with all the spaces.

ngrashia
  • 9,869
  • 5
  • 43
  • 58
ihaveaquestion
  • 1
  • 1
  • I was in process of correcting the question to appear more aesthetically. Didn't realize it would show up like a mess- my first question. Sorry! – ihaveaquestion Nov 04 '14 at 20:45
  • possible duplicate of [Oracle SQL pivot query](http://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) – Ben Nov 05 '14 at 05:54

1 Answers1

1

I believe the best option would be to use max() and group by name:

select a.name, 
       max(case when type = 'Primary' then address_line_1 else null end) as Primary_Address, 
       max(case when type = 'Office' then address_line_1 else null end) as Work_Address
from   address a
group  by a.name
Dave Lyndon
  • 786
  • 5
  • 9
  • Thanks Dave.. When I use max then I get following error- ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function" *Cause: *Action: Error at Line: 12 Column: 1 – ihaveaquestion Nov 05 '14 at 13:57
  • You have to ensure that any field not aggregated (i.e. those not using a max, min, avg... function) is included in the 'group by' clause. For instance in the above example the field not aggregated is a.name, so a.name must be in group by. – Dave Lyndon Nov 05 '14 at 22:03