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 Monday, November 18, 2013 1:16 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:51 PM
Points: 1,967, Visits: 2,905
MAX(age) seems safe enough -- no one gets any younger .
Combining MIN(name) and MAX(name) would allow one name change ... that should handle everyone, right!?


/*
insert into #temp(id,name)
select 2,'Jane Single'
insert into #temp(id,age)
select 2,28
insert into #temp(id,contact_number)
select 2,'123-456-7890'
insert into #temp(id,contact_number)
select 2,'987-654-3210'
insert into #temp(id,name)
select 2,'Jane Married'
*/

SELECT
tGroup.id, ca1.name, tGroup.age, t.contact_number
FROM (
SELECT id, MIN(name) AS name_min, MAX(name) AS name_max, MAX(age) AS age
FROM #temp
GROUP BY id
) AS tGroup
LEFT OUTER JOIN #temp t ON
t.id = tGroup.id
CROSS APPLY (
SELECT DISTINCT name
FROM (
SELECT name_min AS name UNION ALL
SELECT name_max
) AS derived
) AS ca1
WHERE
t.contact_number IS NOT NULL




SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1515339
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse