CoffeeCoder CoffeeCoder - 3 months ago 9
SQL Question

How to return one row with multiple column values?

I'm trying to create a report with one row per customer, however there are multiple rows per customer.

The current view is:

Customer Business Dept Type Status
-----------------------------------------------
019 Public null null null
019 null IT null null
019 null null Retail 0 --char(1)


My desired view is:

Customer Business Dept Type Status
-----------------------------------------------
019 Public IT Retail 0


I'm using SQL Server 2008 R2. There are more columns in my data set, but this is a sample. I'm unsure of how to achieve the results when my datatype is character and not INT based.

Answer

If this is a representative example, and each column will always have a single row with a value and the others will have nulls, you could use an aggregate max or min, which ignore nulls:

SELECT   customer, MAX(business), MAX(dept), MAX(type), MAX(status)
FROM     mytable
GROUP BY customer
Comments