SQL problem - one name 2 address in the same table

Question

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

@user - Is there a maximum of 2 AddressLines per `CName`? Also how do you know which is 1 and which is 2 or is this arbitrary? — Martin Smith, Feb 11 '11 at 20:36
First how are you sure they aren't two different people? Names are not unique. Normally name and address are stored properly in related tables, so people can have multiple addreses. — HLGEM, Feb 11 '11 at 20:37
If you have a pre-set number of addresses, you can self-join as many times as that number. If you dont know that number then www.mongodb.org — chx, Feb 11 '11 at 20:40
@chx: Or you could, you know, properly normalize the address data. Switching to NoSql just because you don't know the number of addresses for a user? Come on. — Adam Robinson, Feb 11 '11 at 20:48
To add some context. This is not the OP's actual scenario I think. They originally posted a question as an answer to this question http://stackoverflow.com/questions/4662464/how-to-select-only-the-first-rows-for-each-unique-value-of-a-column. — Martin Smith, Feb 11 '11 at 20:55

score 2 · Answer 1 · answered Feb 11 '11 at 20:46

2

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!

answered Feb 11 '11 at 20:46

Mohsen Mesgarpour

353
3
12

Adam Robinson · Accepted Answer · 2011-02-11T20:58:59.567

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

@macwadu: You never told us which database system you're using. — Adam Robinson, Feb 12 '11 at 00:50

score 0 · Answer 3 · answered Feb 11 '11 at 20:43

0

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

answered Feb 11 '11 at 20:43

RandomWebGuy

1,439
11
23

this will return 2 records for the combo, like name, a1, a2 and name, a2, a1 – Kris Ivanov Feb 11 '11 at 20:46

score 0 · Answer 4 · answered Feb 17 '11 at 15:05

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

SQL problem - one name 2 address in the same table

4 Answers4