Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Most efficient way to get a single unique combined record for distributed information corresponding to same ID Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 7:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 567, Visits: 270
I have a scenario like this:


create table #temp(id int, name varchar(50), age int, contact_number varchar(50))

insert into #temp(id,name)
select 1,'John'
go
insert into #temp(id,age)
select 1,34
go
insert into #temp(id,contact_number)
select 1,'222-444-5555'


If we query this temporary table we get following output:


id,name,age,contact_number
1,John,NULL,NULL
1,NULL,34,NULL
1,NULL,NULL,222-444-5555



What is the most efficient way of getting the output like this:


id,name,age,contact_number
1,John,34,222-444-5555

Post #1514282
Posted Thursday, November 14, 2013 8:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 13,082, Visits: 12,547
Thanks for posting ddl and sample data. This goes a LONG way to making it easy on us.

This should work for you.

select ID, MAX(name), MAX(age), MAX(contact_number)
from #temp
group by ID



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1514302
Posted Thursday, November 14, 2013 8:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 567, Visits: 270
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.
Post #1514323
Posted Thursday, November 14, 2013 9:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 13,082, Visits: 12,547

I know this is not the proper way of storing data


Phew!!! That is an understatement!!!

So do you have duplicates only on contact_number? If so, you could do something like this:

select ID, MAX(name), MAX(age), x.contact_number
from #temp
cross apply (select contact_number from #temp) x
where x.contact_number is not null
group by ID, x.contact_number



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1514334
Posted Friday, November 15, 2013 11:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 1,061, Visits: 2,574
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





Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1514810
Posted Sunday, November 17, 2013 5:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
If contact number is the only column that may have duplicates, you can also do it like this.

DECLARE @temp TABLE(id int, name varchar(50), age int, contact_number varchar(50));

insert into @temp(id,name) select 1,'John';
insert into @temp(id,age) select 1,34;
insert into @temp(id,contact_number) select 1,'222-444-5555';
insert into @temp(id,contact_number) select 1,'333-444-5555';

SELECT id, name, age, contact_number
FROM
(
SELECT id
,name=MAX(name) OVER (PARTITION BY id)
,age=MAX(age) OVER (PARTITION BY id)
,contact_number
FROM @temp
) a
WHERE contact_number IS NOT NULL;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1515042
Posted Monday, November 18, 2013 12:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 567, Visits: 270
dwain.c (11/17/2013)
If contact number is the only column that may have duplicates, you can also do it like this.

DECLARE @temp TABLE(id int, name varchar(50), age int, contact_number varchar(50));

insert into @temp(id,name) select 1,'John';
insert into @temp(id,age) select 1,34;
insert into @temp(id,contact_number) select 1,'222-444-5555';
insert into @temp(id,contact_number) select 1,'333-444-5555';

SELECT id, name, age, contact_number
FROM
(
SELECT id
,name=MAX(name) OVER (PARTITION BY id)
,age=MAX(age) OVER (PARTITION BY id)
,contact_number
FROM @temp
) a
WHERE contact_number IS NOT NULL;





This is a very good example but what will happen if I have thousands of IDs with such records. This approach is clearly not feasible in a transnational system where these records may keep increasing or updating.
Post #1515099
Posted Monday, November 18, 2013 12:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:24 PM
Points: 567, Visits: 270
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.
Post #1515100
Posted Monday, November 18, 2013 12:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Hold on... rethinking my answer.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1515101
Posted Monday, November 18, 2013 12:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
ksatpute123 (11/18/2013)
dwain.c (11/17/2013)
If contact number is the only column that may have duplicates, you can also do it like this.

DECLARE @temp TABLE(id int, name varchar(50), age int, contact_number varchar(50));

insert into @temp(id,name) select 1,'John';
insert into @temp(id,age) select 1,34;
insert into @temp(id,contact_number) select 1,'222-444-5555';
insert into @temp(id,contact_number) select 1,'333-444-5555';

SELECT id, name, age, contact_number
FROM
(
SELECT id
,name=MAX(name) OVER (PARTITION BY id)
,age=MAX(age) OVER (PARTITION BY id)
,contact_number
FROM @temp
) a
WHERE contact_number IS NOT NULL;





This is a very good example but what will happen if I have thousands of IDs with such records. This approach is clearly not feasible in a transnational system where these records may keep increasing or updating.


Maybe this...

DECLARE @temp TABLE(id int, name varchar(50), age int, contact_number varchar(50));

insert into @temp(id,name) select 1,'John';
insert into @temp(id,age) select 1,34;
insert into @temp(id,contact_number) select 1,'222-444-5555';
insert into @temp(id,contact_number) select 1,'333-444-5555';

SELECT id, name=MAX(name), age=MAX(age), contact_number
FROM
(
SELECT id
,name=MAX(name) OVER (PARTITION BY id)
,age=MAX(age) OVER (PARTITION BY id)
,contact_number
FROM @temp
) a
WHERE contact_number IS NOT NULL
GROUP BY ID, contact_number;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1515106
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse