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.