Home Forums SQL Server 2008 T-SQL (SS2K8) Most efficient way to get a single unique combined record for distributed information corresponding to same ID RE: Most efficient way to get a single unique combined record for distributed information corresponding to same ID

  • wolfkillj (11/15/2013)


    ksatpute123 (11/14/2013)


    Thank you for the quick reply.

    I have already tried using this approach. It is based on the assumption that there will be only one value per column corresponding to a primary key.

    Suppose if I insert one more record in same table like this:

    insert into #temp(id,contact_number)

    select 1,'565-888-9654'

    Then select * from #temp will be interpreted as ID=1 has 2 contact numbers.

    Actual output in this case should be

    id,name,age,contact_number

    1,John,34,222-444-5555

    1,John,34,565-888-9654

    Aggregation will give me only 1 record.

    I know this is not the proper way of storing data but right now I am stuck with something like this.

    This sounds like a nightmare.

    Given the insanity you'd have to confront every time you try to query this data, could you possible do something like this? It's cumbersome, but with some indexing, you could probably get it to perform decently for queries that aren't *too* intensive.

    create table data (id int, name varchar(10), age int, phone varchar(8) )

    go

    insert into data

    values (1, 'jason', null, '987-4522'),

    (1, 'jason', 12, '887-5563'),

    (2, 'harvey', null, '777-9999'),

    (2, null, 37, null),

    (2, null, null, '444-5555'),

    (3, 'harold', 26, null),

    (3, null, 34, '558-8745'),

    (3, null, null, '333-7777')

    go

    create view names as

    select id, name from data where name is not null

    go

    create view age as

    select id, age from data where age is not null

    go

    create view phone as

    select id, phone from data where phone is not null

    go

    create view nameAgePhone as

    select distinct coalesce(n.id, a.id, p.id) as id, n.name, a.age, p.phone

    from names n

    full join age a

    on a.id = n.id

    full join phone p

    on p.id = n.id

    go

    select * from nameAgePhone

    go

    I am right now doing something on the similar lines. Truly a nightmare. I have put in a request to revamp the structure and redesign the architecture but it will take time. Till then stuck with this.