CODEWITHSUNDEEP
sqloracle-database
macwadu
macwadu

Reputation: 907

SQL problem - one name 2 address in the same table

CName           |   AddressLine
-------------------------------
John Smith      | 123 Nowheresville
Jane Doe        | 456 Evergreen Terrace
John Smith      | 999 Somewhereelse
Joe Bloggs      | 1 Second Ave

If i have this table is possible to do a select to put like this

CNAME      | Address1           | Address2

John Smith | 123 Nowheresville  | 999 Somewhereelse

I'm using oracle

Upvotes: 2

Views: 1606

Answers (4)

macwadu
macwadu

Reputation: 907

The problem is resolve, Frank Kulash in oracle forum solved the problem

Here is the solution:

WITH got_r_num  AS
(
    SELECT  cname, addressline
    ,   ROW_NUMBER () OVER ( PARTITION BY  cname
                         ORDER BY      addressline
                   )       AS r_num
    FROM    table_x
--  WHERE   ...     -- If you need any filtering, put it here
)
SELECT    cname
,     MIN (CASE WHEN r_num = 1 THEN addressline END)    AS addressline1
,     MIN (CASE WHEN r_num = 2 THEN addressline END)    AS addressline2
FROM      got_r_num
GROUP BY  cname

Tanks to all for the help

Upvotes: 0

Adam Robinson
Adam Robinson

Reputation: 185683

As your table stands, you cannot use a simple self-join to reduce this to a single line. You can bring back rows that have all of the addresses (so long as you hard-code for a particular maximum number of addresses), but you will always have the same number of rows as there are addresses for a given user (unless you have a way of identifying a single address as "primary").

In order to reduce your result set to a single line, you'll have to provide some way of marking a "first" address. With SQL Server (or similar professional-grade RDBM's), you could use a common table expression with ranking/row numbering functions to do this:

with Addresses as
(select 
    CName, 
    AddressLine, 
    row_number() over (partition by CName order by AddressLine) as RowNum

from YourTable)
select
    a1.CName,
    a1.AddressLine as Address1,
    a2.AddressLine as Address2,
    a3.AddressLine as Address3

from Addresses a1

left join Addresses a2 on a2.CName = a1.CName and a2.RowNum = 2
left join Addresses a3 on a3.CName = a1.CName and a3.RowNum = 3

where a1.RowNum = 1

Upvotes: 1

Mohsen Mesgarpour
Mohsen Mesgarpour

Reputation: 353

It is considered a bad design (inefficient memory usage) to add a new column for appearance of duplications in just some rows . Maybe you should consider using inner-join and a separate table for the address column!

Upvotes: 2

RandomWebGuy
RandomWebGuy

Reputation: 1449

temp = your table name

select distinct cname, addressline as [address1],
( 
ISNULL((select addressline from temp where cname = t.cname and addressline != t.addressline), '')
) as address2
from
temp t

Upvotes: 0

Related Questions