SQL Server duplicate removal with a twist

Question

SQL Server 2008, I have inherited a table like this (74k rows):

create table #mess (keycol char(36) , name1 varchar(254) , name2 varchar(254), valuex varchar(254) )
insert into #mess values ('971EC307-8514-450D-AE3A-4E25EA3F3A10' , 'a' , '' , '' )
insert into #mess values ('971EC307-8514-450D-AE3A-4E25EA3F3A10' , '' , 'a' , 'value-a' )
insert into #mess values ('04FD0C0B-FC90-405A-BFD6-C3AF2516E51F' , 'b' , '' , 'value-b' )
insert into #mess values ('578F2893-15E6-4877-9FE6-AC2F4F351143' , 'c' , '' , 'value-c' )
insert into #mess values ('04FD0C0B-FC90-405A-BFD6-C3AF2516E51F' , '' , 'b' , '' )
insert into #mess values ('FAFCBDFE-D49E-4566-882D-0B6628DA59CC' , '' , 'd' , 'value-d' )

Which makes this result set:

keycol                               name1   name2    valuex
------------------------------------ ------- -------- ------------
971EC307-8514-450D-AE3A-4E25EA3F3A10 a                
971EC307-8514-450D-AE3A-4E25EA3F3A10         a        value-a
04FD0C0B-FC90-405A-BFD6-C3AF2516E51F b                value-b
578F2893-15E6-4877-9FE6-AC2F4F351143 c                value-c
04FD0C0B-FC90-405A-BFD6-C3AF2516E51F         b        
FAFCBDFE-D49E-4566-882D-0B6628DA59CC         d        value-d

I need to make it like this (de-duplicating and collapsing the data, based on when keycol matches between two rows, and using name1 if name2 is empty and vice versa but always using name1 and always using non-empty valuex column). All ideas appreciated.

Thanks.

keycol                               name1   valuex
------------------------------------ ------- ------------
971EC307-8514-450D-AE3A-4E25EA3F3A10 a       value-a
04FD0C0B-FC90-405A-BFD6-C3AF2516E51F b       value-b
578F2893-15E6-4877-9FE6-AC2F4F351143 c       value-c
FAFCBDFE-D49E-4566-882D-0B6628DA59CC d       value-d

are the blanks null or empty string? – SQLMason Jul 25 '11 at 16:58 — SQLMason, Jul 25 '11 at 16:58
They are empty strings. Thanks. – Snowy Jul 25 '11 at 18:54 — Snowy, Jul 25 '11 at 18:54

Chains · Answer 1 · 2011-07-26T15:01:23.817

BTW -- The 3 columns on the right are, essentially, identical. You can drop the right-most (values like 'value-a', etc.) with no penalty, then, combine the other two.

Anyway -- a couple of different ways -- using DISTINCT, or alternatively, GROUP BY to get what you want:

Using DISTINCT:

SELECT DISTINCT
   keycol, 
   Coalesce(NULLIF(name1,''), name2) as name1,
   'Value-' + Coalesce(NULLIF(name1,''), name2) as valuex
FROM
   Table

Using GROUP BY:

SELECT
   keycol, 
   Coalesce(max(nullif(name1,'')), max(name2)) as name1,
   max(valuex)
FROM
   table
Group By
   keycol

Marc B · Answer 2 · 2011-07-25T16:54:49.273

0

You can use the COALESCE() function. It takes any number of arguments, and returns the first non-NULL value as its result.

edit: ~~SELECT keycol, COALESCE(name1, name2), valuex GROUP BY keycol~~

Ok. Obviously wrong.. thanks for pointing it out. I'll just go finish waking up now.... mondays...

edited Jul 25 '11 at 16:54

answered Jul 25 '11 at 16:09

Marc B

356,200
43
426
500

I think you need to add valuex to groupby in your example, or possibly add Coalesce to it. Great explanation btw. – Brian Webster Jul 25 '11 at 16:16
and the COALESCE field -- you can't use any non-aggregate fields in a GROUP BY unless they're in the GROUP BY clause. – mwigdahl Jul 25 '11 at 16:22
This syntax is not valid in SQL Server, since you are grouping by keycol, but you are not using an aggregating function one the other columns – Lamak Jul 25 '11 at 16:36
Not going to work. Test this out to see why: SELECT COALESCE('','a'). Also, the group by is not going to merge records together -- which still needs to happen for valuex. – Chains Jul 25 '11 at 16:49

score 0 · Answer 3 · answered Jul 25 '11 at 16:18

Something like this?

create table #mess (keycol char(36) , name1 varchar(254) , name2 varchar(254), valuex varchar(254) )
insert into #mess values ('971EC307-8514-450D-AE3A-4E25EA3F3A10' , 'a' , '' , '' )
insert into #mess values ('971EC307-8514-450D-AE3A-4E25EA3F3A10' , '' , 'a' , 'value-a' )
insert into #mess values ('04FD0C0B-FC90-405A-BFD6-C3AF2516E51F' , 'b' , '' , 'value-b' )
insert into #mess values ('578F2893-15E6-4877-9FE6-AC2F4F351143' , 'c' , '' , 'value-c' )
insert into #mess values ('04FD0C0B-FC90-405A-BFD6-C3AF2516E51F' , '' , 'b' , '' )
insert into #mess values ('FAFCBDFE-D49E-4566-882D-0B6628DA59CC' , '' , 'd' , 'value-d' )

select * from #mess

select 
    n1.keycol, coalesce(n1.name1, n2.name1), n1.valuex 
from
    (
        select keycol, max(nullif(name1, '')) name1, max(nullif(valuex, '')) valuex
        from #mess 
        group by keycol) n1
    inner join  (
        select keycol, max(nullif(name2, '')) name1, max(nullif(valuex, '')) valuex
        from #mess 
        group by keycol) n2
        on  n1.keycol = n2.keycol

drop table #mess

mwigdahl · Answer 4 · 2011-07-25T20:30:37.613

It looks as if the data structure is such that you always just want the non-NULL valuex row for each keycol. You should be able to achieve this, and collapse name1 and name2 together, by eliminating the NULL valuex rows with a UNION like so:

SELECT keycol, name1, valuex
FROM tablename
WHERE valuex != '' AND name1 != ''
UNION
SELECT keycol, name2 AS name1, valuex
FROM tablename
WHERE valuex != '' AND name2 != ''

If there is always just one valid valuex row for each keycol, this should work. If it isn't the case that this is how your data is laid out, let me know and I'll modify further.

SQL Server duplicate removal with a twist

4 Answers4