如何在SQL中将多个行合并为一个

Question

我可能已经读了十几个类似的线程，但是我一直无法使用它们来解决我的问题。我很接近，也许离解决方案只有一两步之遥。

我有我加载数据到这类似于这样的表：

​​

我想要的是将它们组合成一个单一行：

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

的序列号是唯一的所以我尝试使用T1.Serial = T2.Serial和T1.Serial = T3.Serial内部连接T1至T2以及T1至T3，但仍然使用NULLS获取行，例如Erikas返回5行，其中只有一行拥有所有数据

我试着用在哪里 “T1.MonthlyCount”（ETC）IS NOT NULL，但没有任何筛选出的行：

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 

什么我得到的是：

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 

我只查找标记为Expected的上述3行。任何援助将不胜感激。

Answer 1

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 

Answer 2

使用条件聚合，并且只按您希望在每一行中唯一的列进行聚合。而且，你有更多的联结，而不是必要的。没有必要。

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; 

如果你想NULL，而不是0，请从每个条款的else 0。

Here是一个SQL小提琴，演示它的工作原理。