"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:<4uydnUP5vLe0C-CiRVn-tQ@giganews.com>...
> SELECT S.symbol,
> COALESCE(T.xidentity,S.xidentity), COALESCE(T.idsource,S.idsource),
> COALESCE(T.exchange,S.exchange), COALESCE(T.type,S.type),
> COALESCE(T.subtype,S.subtype), COALESCE(T.xname,S.xname)
> FROM Stocks AS S
> JOIN
> (SELECT symbol,
> CASE COUNT(DISTINCT NULLIF(xidentity,''))
> 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')

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.