How can I select only common properties from a results set?

Question

Lets say I have a table with columns such as:

• ID
• Name
• City
• State
• ZIP

I need to write a query that will return only one row. This row will include City, State, and ZIP, but I only want a field to have a value if all values in the results set are the same, otherwise I want the field to be null.

For example, if every record has the same State, then State would be in the result returned. If just one of the results has a different state, I want the field to be null. Is something like this possible in SQL Server 2005?

Basically, I want a query like this:

SELECT City, State, ZIP
FROM Users
WHERE ID IN(1,2,3,4,5,6)

But only return a single row, with the specs I described above.

Answer 1

SELECT
    CASE WHEN COUNT(DISTINCT city) = 1 THEN MAX(city) ELSE NULL END AS city,
    CASE WHEN COUNT(DISTINCT state) = 1 THEN MAX(state) ELSE NULL END AS state,
    CASE WHEN COUNT(DISTINCT zip) = 1 THEN MAX(zip) ELSE NULL END AS zip
FROM Users
WHERE ID IN(1,2,3,4,5,6)

After other answer:

SELECT
    CASE WHEN COUNT(DISTINCT NULLIF(city,'**NULL**')) = 1 THEN MAX(city) ELSE NULL END AS city,
    CASE WHEN COUNT(DISTINCT NULLIF(state,'**NULL**')) = 1 THEN MAX(state) ELSE NULL END AS state,
    CASE WHEN COUNT(DISTINCT NULLIF(zip,'**NULL**')) = 1 THEN MAX(zip) ELSE NULL END AS zip
FROM Users
WHERE ID IN(1,2,3,4,5,6)

Answer 2

If columns are not nullable, or if you want to ignore nulls then gbn's answer is correct. If, however, you need to treat null values as different values, try this:

CASE WHEN COUNT(DISTINCT city) = 1 
      AND COUNT(city) = COUNT(*) 
     THEN MAX(city) ELSE NULL END AS city