CODEWITHSUNDEEP
sqlsql-serverselect
CoffeeCoder
CoffeeCoder

Reputation: 303

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.

Upvotes: 1

Views: 1250

Answers (2)

user6697658
user6697658

Reputation: 11

try something this:

CREATE TABLE #tmp ([Customer] CHAR(3), [Business] VARCHAR(20), [Dept] VARCHAR(20), [Type] VARCHAR(20), [Status] CHAR(1))
INSERT INTO #tmp (Customer, Business) VALUES (  '019',  'Public')
INSERT INTO #tmp (Customer,Dept) VALUES ('019','IT')
INSERT INTO #tmp (Customer,[Type]) VALUES ('019','Retail')
INSERT INTO #tmp (Customer,[Status]) VALUES ('019','0')
SELECT * FROM #tmp AS t

SELECT t.Customer, t.Business, t2.Dept, t3.[Type], t4.[Status] FROM #tmp AS t 
JOIN #tmp AS t2 ON t2.Customer = t.Customer 
JOIN #tmp AS t3 ON t3.Customer = t.Customer
JOIN #tmp AS t4 ON t4.Customer = t.Customer
WHERE t.Business IS NOT NULL AND t2.Dept IS NOT NULL AND t3.[Type] IS NOT NULL AND t4.[Status] IS NOT NULL

Upvotes: 1

Mureinik
Mureinik

Reputation: 311143

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

Upvotes: 4

Related Questions