SQL从一个表中获取计数并拆分为两列

Question

我有一个这样的表:

Date        Product
1/1/2015    Apples
1/1/2015    Apples
1/1/2015    Oranges
1/2/2015    Apples
1/2/2015    Apples
1/2/2015    Oranges

我怎样才能做一个选择，所以我得到这样的东西:

Date      Count of Apples    Count of Oranges
1/1/2015      2                 1
1/2/2015      2                 1

谢谢。我试过这样的情况，但抛出了错误:

Select 'Date',
CASE WHEN 'Product' = 'Apples' THEN COUNT(*) ELSE 0 END as 'Count'
FROM #TEMP Group by 1,2

每个 GROUP BY 表达式必须至少包含一个不是外部引用的列。

Answer 1

您可以像这样进行条件聚合:

select 
  [date], 
  sum(case when Product = 'Apples'  then 1 else 0 end) as [Count of Apples],
  sum(case when Product = 'Oranges' then 1 else 0 end) as [Count of Oranges]
from #temp
group by [date]