CODEWITHSUNDEEP
sql
user2233506
user2233506

Reputation: 81

How to combine multiple rows into one in SQL

I've probably read a dozen or so similar threads but I have not been able to use them to resolve my issue. I am close, maybe just a step or two away from the solution.

I have a table that I loaded data into which looks similar to this:

CustName | Model | Serial | Color | MonthlyCount
Freds    | 123AB | L23456 | BLUE  | 987
Freds    | 123AB | L23456 | GREEN | 1127
Jimmys   | 111SS | L11234 | BLUE  | 2245
Erikas   | 123AB | L11331 | RED   | 12
Erikas   | 123AB | L11331 | BLUE  | 10
Erikas   | 123AB | L11331 | GREEN | 19

What I want is to combine them into a single row:

CustName | Model | Serial | GreenCount | BlueCount | RedCount
Freds    | 123AB | L23456 | 987        | 1127      |
Jimmys   | 111SS | L11234 |            | 2245      |
Erikas   | 123AB | L11331 | 19         | 10        | 12

The Serial numbers are unique so I tried to Inner Join T1 to T2 and T1 to T3 using T1.Serial = T2.Serial and T1.Serial = T3.Serial but I'm still getting rows with NULLS, for example Erikas returns 5 rows, only one of which has all of the data in it

I tried using Where "T1.MonthlyCount"(etc) IS NOT NULL but that didn't filter any rows out:

Select T1.CustName
       ,T1.Model 
       ,T1.Serial
       ,(Select T1.MonthlyCount Where T1.Color = 'BLUE') As BlueCount
       ,(Select T2.MonthlyCount Where T2.Color = 'GREEN') As GreenCount
       ,(Select T3.MonthlyCount Where T3.Color = 'RED') As RedCount
From Table1 T1

Inner Join Table2 T2
on T1.Serial = T2.Serial

Inner Join Table3 T3
on T1.Serial = T3.Serial

Where T1.MonthlyCount IS NOT NULL
AND T2.MonthlyCount IS NOT NULL
AND T3.MonthlyCount IS NOT NULL

Group By Serial, CustName, Model, Color, MonthlyCount

What I am getting is this:

CustName | Model | Serial | GreenCount | BlueCount | RedCount
Freds    | 123AB | L23456 | NULL       | NULL      | NULL
Freds    | 123AB | L23456 | 987        | NULL      | NULL
Freds    | 123AB | L23456 | NULL       | 1127      | NULL
Freds    | 123AB | L23456 | 987        | 1127      | NULL    <--Expected
Jimmys   | 111SS | L11234 | NULL       | NULL      | NULL
Jimmys   | 111SS | L11234 | NULL       | 2245      | NULL    <--Expected
Erikas   | 123AB | L11331 | NULL       | NULL      | NULL
Erikas   | 123AB | L11331 | 19         | NULL      | NULL
Erikas   | 123AB | L11331 | NULL       | 10        | NULL
Erikas   | 123AB | L11331 | NULL       | NULL      | 12
Erikas   | 123AB | L11331 | 19         | 10        | 12      <--Expected

I am only looking for the above 3 rows marked as Expected. Any assistance would be greatly appreciated.

Upvotes: 0

Views: 90

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

Use conditional aggregation and only aggregate by the columns you want to be unique in each row. And, you have way more joins than necessary. None are needed.

Select T1.CustName, T1.Model, T1.Serial,
       sum(case when T1.Color = 'BLUE' then t1.MonthlyCount else 0 end) As BlueCount,
       sum(case when T1.Color = 'GREEN' then t1.MonthlyCount else 0 end) As GreenCount,
       sum(case when T1.Color = 'RED' then t1.MonthlyCount else 0 end) As RedCount
From Table1 T1 
Group By T1.CustName, T1.Model, T1.Serial;

If you want NULL instead of 0, remove the else 0 from each of the clauses.

Here is a SQL Fiddle demonstrating that it works.

Upvotes: 4

FuzzyTree
FuzzyTree

Reputation: 32402

Select T1.CustName
       ,T1.Model 
       ,T1.Serial
       ,SUM(CASE WHEN Color = 'Blue' THEN MonthlyCount ELSE 0 END) BlueCount
       ,SUM(CASE WHEN Color = 'Green' THEN MonthlyCount ELSE 0 END) GreenCount
       ,SUM(CASE WHEN Color = 'Red' THEN MonthlyCount ELSE 0 END) RedCount
From Table1 T1
Group By Serial, CustName, Model

Upvotes: 3

Related Questions