Merging rows within same table

[Jason]

I need to populate a table from several sources of raw data. For a
given security (stock) it is possible to only receive PARTS of
information from each of the different sources. It is also possible
to have conflicting data.

I am looking to make a composite picture of a given security using the
following rules:

1) The goal is to replace all NULL and Blank values with data

2) Order of precedence (from highest to lowest) is Non-NULL Non-Blank
--> Blank --> NULL

3) In the case of Non-NULL Non-Blank values that conflict (are
different) leave existing value (even if NULL or Blank)

For example:

Given the following rows:

Symbol Identity IdSource Exchange Type SubType Name
-------- ------------ --------- --------- ------- ---------
------------------
TZA 901145102 CUSIP XNYS Stock NULL TV AZTECA
TZA 901145102 NULL NULL NULL NULL

WSM 969904101 CUSIP XNYS Stock NULL WILLIAMS
SONOMA
WSM 969904101 NULL XNYS Stock NULL
WILLIAMS-SONOMA
WSM CUSIP XNYS Stock Common NULL
WSM NULL CUSIP XASE Stock NULL WILLIAMS
SONOMA

TYC 902124106 CUSIP XNYS Stock NULL TYCO
TYC 902124106 CUSIP XNYS Stock NULL TYCO
INTERNATIONAL


I am looking for the following results ('*' indicates changed value)

Symbol Identity IdSource Exchange Type SubType Name
-------- ------------ --------- --------- ------- ---------
------------------
TZA 901145102 CUSIP XNYS Stock NULL TV AZTECA
TZA 901145102 *CUSIP *XNYS *Stock NULL *TV AZTECA

WSM 969904101 CUSIP XNYS Stock *Common WILLIAMS
SONOMA
WSM 969904101 *CUSIP XNYS Stock *Common
WILLIAMS-SONOMA
WSM *969904101 CUSIP NULL Stock Common NULL
WSM *969904101 CUSIP XASE Stock *Common WILLIAMS
SONOMA

TYC 902124106 CUSIP XNYS Stock NULL TYCO
TYC 902124106 CUSIP XNYS Stock NULL TYCO
INTERNATIONAL

[David Portas]

Re: Merging rows within same table


SELECT S.symbol,
COALESCE(T.xide ntity,S.xidenti ty), COALESCE(T.idso urce,S.idsource ),
COALESCE(T.exch ange,S.exchange ), COALESCE(T.type ,S.type),
COALESCE(T.subt ype,S.subtype), COALESCE(T.xnam e,S.xname)
FROM Stocks AS S
JOIN
(SELECT symbol,
CASE COUNT(DISTINCT NULLIF(xidentit y,''))
WHEN 1 THEN MAX(xidentity) END,
CASE COUNT(DISTINCT NULLIF(idsource ,''))
WHEN 1 THEN MAX(idsource) END,
CASE COUNT(DISTINCT NULLIF(exchange ,''))
WHEN 1 THEN MAX(exchange) END,
CASE COUNT(DISTINCT NULLIF(type,'') )
WHEN 1 THEN MAX(type) END,
CASE COUNT(DISTINCT NULLIF(subtype, ''))
WHEN 1 THEN MAX(subtype) END,
CASE COUNT(DISTINCT NULLIF(xname,'' ))
WHEN 1 THEN MAX(xname) END
FROM Stocks
GROUP BY symbol) AS T
(symbol, xidentity, idsource, exchange, type, subtype, xname)
ON S.symbol = T.symbol

Help others to help you by posting DDL for your table(s) and including
sample data as INSERT statements. That way people can test results and don't
have to guess at datatypes, constraints and keys:

CREATE TABLE Stocks (symbol CHAR(4) NOT NULL, xidentity CHAR(9) NULL,
idsource CHAR(5) NULL, exchange CHAR(4) NULL, type CHAR(5) NULL, subtype
CHAR(6) NULL, xname CHAR(20) NULL /* PRIMARY KEY ??? */)

INSERT INTO Stocks VALUES
('TZA', '901145102', 'CUSIP', 'XNYS', 'Stock', NULL, 'TV AZTECA')
INSERT INTO Stocks VALUES
('TZA', '901145102', NULL, NULL, '', NULL, NULL)
INSERT INTO Stocks VALUES
('WSM', '969904101', 'CUSIP', 'XNYS', 'Stock', NULL, 'WILLIAMS SONOMA')
INSERT INTO Stocks VALUES
('WSM', '969904101', NULL, 'XNYS', 'Stock', NULL, 'WILLIAMS-SONOMA')
INSERT INTO Stocks VALUES
('WSM', '', 'CUSIP', 'XNYS', 'Stock', 'Common', NULL)
INSERT INTO Stocks VALUES
('WSM', NULL, 'CUSIP', 'XASE', 'Stock', NULL, 'WILLIAMS SONOMA')
INSERT INTO Stocks VALUES
('TYC', '902124106', 'CUSIP', 'XNYS', 'Stock', NULL, 'TYCO')
INSERT INTO Stocks VALUES
('TYC', '902124106', 'CUSIP', 'XNYS', 'Stock', NULL, 'TYCO INTERNATIONAL')

--
David Portas
------------
Please reply only to the newsgroup
--

[Jason]

Re: Merging rows within same table

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message news:<4uydnUP5v Le0C-CiRVn-tQ@giganews.com >...[color=blue]
> SELECT S.symbol,
> COALESCE(T.xide ntity,S.xidenti ty), COALESCE(T.idso urce,S.idsource ),
> COALESCE(T.exch ange,S.exchange ), COALESCE(T.type ,S.type),
> COALESCE(T.subt ype,S.subtype), COALESCE(T.xnam e,S.xname)
> FROM Stocks AS S
> JOIN
> (SELECT symbol,
> CASE COUNT(DISTINCT NULLIF(xidentit y,''))
> WHEN 1 THEN MAX(xidentity) END,
> CASE COUNT(DISTINCT NULLIF(idsource ,''))
> WHEN 1 THEN MAX(idsource) END,
> CASE COUNT(DISTINCT NULLIF(exchange ,''))
> WHEN 1 THEN MAX(exchange) END,
> CASE COUNT(DISTINCT NULLIF(type,'') )
> WHEN 1 THEN MAX(type) END,
> CASE COUNT(DISTINCT NULLIF(subtype, ''))
> WHEN 1 THEN MAX(subtype) END,
> CASE COUNT(DISTINCT NULLIF(xname,'' ))
> WHEN 1 THEN MAX(xname) END
> FROM Stocks
> GROUP BY symbol) AS T
> (symbol, xidentity, idsource, exchange, type, subtype, xname)
> ON S.symbol = T.symbol
>
> Help others to help you by posting DDL for your table(s) and including
> sample data as INSERT statements. That way people can test results and don't
> have to guess at datatypes, constraints and keys:
>
> CREATE TABLE Stocks (symbol CHAR(4) NOT NULL, xidentity CHAR(9) NULL,
> idsource CHAR(5) NULL, exchange CHAR(4) NULL, type CHAR(5) NULL, subtype
> CHAR(6) NULL, xname CHAR(20) NULL /* PRIMARY KEY ??? */)
>
> INSERT INTO Stocks VALUES
> ('TZA', '901145102', 'CUSIP', 'XNYS', 'Stock', NULL, 'TV AZTECA')
> INSERT INTO Stocks VALUES
> ('TZA', '901145102', NULL, NULL, '', NULL, NULL)
> INSERT INTO Stocks VALUES
> ('WSM', '969904101', 'CUSIP', 'XNYS', 'Stock', NULL, 'WILLIAMS SONOMA')
> INSERT INTO Stocks VALUES
> ('WSM', '969904101', NULL, 'XNYS', 'Stock', NULL, 'WILLIAMS-SONOMA')
> INSERT INTO Stocks VALUES
> ('WSM', '', 'CUSIP', 'XNYS', 'Stock', 'Common', NULL)
> INSERT INTO Stocks VALUES
> ('WSM', NULL, 'CUSIP', 'XASE', 'Stock', NULL, 'WILLIAMS SONOMA')
> INSERT INTO Stocks VALUES
> ('TYC', '902124106', 'CUSIP', 'XNYS', 'Stock', NULL, 'TYCO')
> INSERT INTO Stocks VALUES
> ('TYC', '902124106', 'CUSIP', 'XNYS', 'Stock', NULL, 'TYCO INTERNATIONAL')[/color]

Thank you very much David. Your solution was clean and efficient. I
thought of using a join but did not even think about that mixture of
case, nullif, max, etc.

[Jason]

Re: Merging rows within same table

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message news:<4uydnUP5v Le0C-CiRVn-tQ@giganews.com >...[color=blue]
> SELECT S.symbol,
> COALESCE(T.xide ntity,S.xidenti ty), COALESCE(T.idso urce,S.idsource ),
> COALESCE(T.exch ange,S.exchange ), COALESCE(T.type ,S.type),
> COALESCE(T.subt ype,S.subtype), COALESCE(T.xnam e,S.xname)
> FROM Stocks AS S
> JOIN
> (SELECT symbol,
> CASE COUNT(DISTINCT NULLIF(xidentit y,''))
> WHEN 1 THEN MAX(xidentity) END,
> CASE COUNT(DISTINCT NULLIF(idsource ,''))
> WHEN 1 THEN MAX(idsource) END,
> CASE COUNT(DISTINCT NULLIF(exchange ,''))
> WHEN 1 THEN MAX(exchange) END,
> CASE COUNT(DISTINCT NULLIF(type,'') )
> WHEN 1 THEN MAX(type) END,
> CASE COUNT(DISTINCT NULLIF(subtype, ''))
> WHEN 1 THEN MAX(subtype) END,
> CASE COUNT(DISTINCT NULLIF(xname,'' ))
> WHEN 1 THEN MAX(xname) END
> FROM Stocks
> GROUP BY symbol) AS T
> (symbol, xidentity, idsource, exchange, type, subtype, xname)
> ON S.symbol = T.symbol
>
> Help others to help you by posting DDL for your table(s) and including
> sample data as INSERT statements. That way people can test results and don't
> have to guess at datatypes, constraints and keys:
>
> CREATE TABLE Stocks (symbol CHAR(4) NOT NULL, xidentity CHAR(9) NULL,
> idsource CHAR(5) NULL, exchange CHAR(4) NULL, type CHAR(5) NULL, subtype
> CHAR(6) NULL, xname CHAR(20) NULL /* PRIMARY KEY ??? */)
>
> INSERT INTO Stocks VALUES
> ('TZA', '901145102', 'CUSIP', 'XNYS', 'Stock', NULL, 'TV AZTECA')
> INSERT INTO Stocks VALUES
> ('TZA', '901145102', NULL, NULL, '', NULL, NULL)
> INSERT INTO Stocks VALUES
> ('WSM', '969904101', 'CUSIP', 'XNYS', 'Stock', NULL, 'WILLIAMS SONOMA')
> INSERT INTO Stocks VALUES
> ('WSM', '969904101', NULL, 'XNYS', 'Stock', NULL, 'WILLIAMS-SONOMA')
> INSERT INTO Stocks VALUES
> ('WSM', '', 'CUSIP', 'XNYS', 'Stock', 'Common', NULL)
> INSERT INTO Stocks VALUES
> ('WSM', NULL, 'CUSIP', 'XASE', 'Stock', NULL, 'WILLIAMS SONOMA')
> INSERT INTO Stocks VALUES
> ('TYC', '902124106', 'CUSIP', 'XNYS', 'Stock', NULL, 'TYCO')
> INSERT INTO Stocks VALUES
> ('TYC', '902124106', 'CUSIP', 'XNYS', 'Stock', NULL, 'TYCO INTERNATIONAL')[/color]

I did forget to ask a question. When I run your code I receive the
following message: 'Warning: Null value is eliminated by an aggregate
or other SET operation.'

Should I care about this?

[Erland Sommarskog]

Re: Merging rows within same table

Jason (JayCallas@hotm ail.com) writes:[color=blue]
> I did forget to ask a question. When I run your code I receive the
> following message: 'Warning: Null value is eliminated by an aggregate
> or other SET operation.'
>
> Should I care about this?[/color]

No. The cause are these expressions:

COUNT(DISTINCT NULLIF(subtype, ''))

You might be able to rewrite this, but I leave that to David. :-) If the
messages bother you, you can embed the query with SET ANSI_WARNINGS OFF and
SET ANSI_WARNINGS ON.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Jason]

Re: Merging rows within same table

JayCallas@hotma il.com (Jason) wrote in message news:<f01a7c89. 0310061203.24c9 43e@posting.goo gle.com>...[color=blue]
> "David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message news:<4uydnUP5v Le0C-CiRVn-tQ@giganews.com >...[color=green]
> > SELECT S.symbol,
> > COALESCE(T.xide ntity,S.xidenti ty), COALESCE(T.idso urce,S.idsource ),
> > COALESCE(T.exch ange,S.exchange ), COALESCE(T.type ,S.type),
> > COALESCE(T.subt ype,S.subtype), COALESCE(T.xnam e,S.xname)
> > FROM Stocks AS S
> > JOIN
> > (SELECT symbol,
> > CASE COUNT(DISTINCT NULLIF(xidentit y,''))
> > WHEN 1 THEN MAX(xidentity) END,
> > CASE COUNT(DISTINCT NULLIF(idsource ,''))
> > WHEN 1 THEN MAX(idsource) END,
> > CASE COUNT(DISTINCT NULLIF(exchange ,''))
> > WHEN 1 THEN MAX(exchange) END,
> > CASE COUNT(DISTINCT NULLIF(type,'') )
> > WHEN 1 THEN MAX(type) END,
> > CASE COUNT(DISTINCT NULLIF(subtype, ''))
> > WHEN 1 THEN MAX(subtype) END,
> > CASE COUNT(DISTINCT NULLIF(xname,'' ))
> > WHEN 1 THEN MAX(xname) END
> > FROM Stocks
> > GROUP BY symbol) AS T
> > (symbol, xidentity, idsource, exchange, type, subtype, xname)
> > ON S.symbol = T.symbol
> >[/color][/color]

Hit another small issue. For SOME (in this case [type]) columns I need
to set a priority. If two rows have conflicting data (where COUNT > 1)
on a particular column, I want to use the value from the first row in
the set. (I would make sure that rows get inserted in the order I of
priority.) I thought of using TOP 1 somehow but cannot figure out how
to replace the MAX function with it (I know MAX is a function while
TOP is a statement).

[David Portas]

Re: Merging rows within same table

> on a particular column, I want to use the value from the first row in[color=blue]
> the set. (I would make sure that rows get inserted in the order I of
> priority.) I thought of using TOP 1 somehow but cannot figure out how[/color]

A table has no inherent ordering so you will have to add a column to
identify the sequence. Here's an example using Seq_No as a sequence number:

CREATE TABLE Stocks (symbol CHAR(4) NOT NULL, xidentity CHAR(9) NULL,
idsource CHAR(5) NULL, exchange CHAR(4) NULL, type CHAR(5) NULL, subtype
CHAR(6) NULL, xname CHAR(20) NULL, seq_no INTEGER NOT NULL UNIQUE /* PRIMARY
KEY ??? */)

I guess that you actually want the to take the value from the first row
which has a *populated* value for the column:

SELECT S.symbol,
COALESCE(T.xide ntity,S.xidenti ty), COALESCE(T.idso urce,S.idsource ),
COALESCE(T.exch ange,S.exchange ), M.type,
COALESCE(T.subt ype,S.subtype), COALESCE(T.xnam e,S.xname)
FROM Stocks AS S
JOIN
(SELECT symbol,
CASE COUNT(DISTINCT NULLIF(xidentit y,''))
WHEN 1 THEN MAX(xidentity) END,
CASE COUNT(DISTINCT NULLIF(idsource ,''))
WHEN 1 THEN MAX(idsource) END,
CASE COUNT(DISTINCT NULLIF(exchange ,''))
WHEN 1 THEN MAX(exchange) END,
CASE COUNT(DISTINCT NULLIF(subtype, ''))
WHEN 1 THEN MAX(subtype) END,
CASE COUNT(DISTINCT NULLIF(xname,'' ))
WHEN 1 THEN MAX(xname) END,
MIN(CASE WHEN type>'' THEN seq_no END)
FROM Stocks
GROUP BY symbol) AS T
(symbol, xidentity, idsource, exchange, subtype, xname, seq_no)
ON S.symbol = T.symbol
LEFT JOIN Stocks AS M
ON T.seq_no = M.seq_no

--
David Portas
------------
Please reply only to the newsgroup
--