How to retrieve only values common to all rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to retrieve only values common to all rows

Author

Topic

soaphope
Starting Member

13 Posts

Posted - 2011-01-21 : 22:00:11

I'm trying to produce a single row result from a set of rows, where each column in the result set contains the value of the input set if the entire column is the same, or NULL otherwise. In other words, the columns in the input set that are identical on all the rows.

Example1

input set:
id status name
1 new john
2 new mary
3 new seth

output:
NULL new NULL

example 2:
input set:
id status name
1 new john
2 old john
3 new john

output:
NULL NULL john

Any guidance would be greatly appreciated!

Salah Boukadoum
Founder, Soap Hope
Where 100% of profits go to lift women from poverty - http://soaphope.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-21 : 22:35:58

GROUP BY would reduce duplicate rows into a single row. I am not sure I follow your example..where do the NULL's come from..your sample data has data in each row.

Poor planning on your part does not constitute an emergency on my part.

soaphope
Starting Member

13 Posts

Posted - 2011-01-22 : 00:03:46

The NULLs are what I want as a result, they are not in the input data. I am asking for the SQL that would produce them. I'm wanting a result that consists of one row where each column in the row is either NULL, if the values in that column in the input rows are not all the same value, or else the value that is consistently in that column in the input rows. If this isn't making sense I can post more examples. Thanks for your thoughts on this.

soaphope
Starting Member

13 Posts

Posted - 2011-01-22 : 01:50:01

Well I'm not sure if this is a "good" way to do it, but it achieves the result. From my example above with id, status, name from table 'ztest':

SELECT
'cid' = CASE WHEN (SELECT COUNT(DISTINCT id) FROM ztest) = 1 THEN (select TOP 1 id FROM ztest) END,
'cstatus' = CASE WHEN (SELECT COUNT(DISTINCT status) FROM ztest)=1 then (SELECT TOP 1 status FROM ztest) END,
'cname' = CASE WHEN (SELECT COUNT(DISTINCT name) FROM ztest)=1 then (SELECT TOP 1 name FROM ztest) END

If someone with more experience can comment on this approach or offer a better one, i would appreciate it. Thank you!

Salah Boukadoum
Founder, Soap Hope
Where 100% of profits go to lift women from poverty - http://soaphope.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources