sql-server - 如何同时对具有 2 个值的列进行分组和计数？

Question

我有一个名为 campaign 的表。这是它的样子:

website event type
amazon  imp
amazon  imp
amazon  imp
amazon  click
apple   click
apple   imp
adidas  click
adidas  imp
adidas  click
adidas  imp

我想按事件类型列中的不同值对其进行分组，然后计算事件类型中的不同值。我的表格最终应该是这样的:

website imp click
amazon  3   1
apple   1   1
adidas  2   2

我该怎么做？

编辑 2019 年 3 月 14 日:

到目前为止我到了哪里:

  SELECT a.[webite], case when a.[event_type] = 'imp' then 1 end as 'imp', case when a.[event_type] = 'click' then 1 end as 'click'
  FROM [campaign] as a
  INNER JOIN [campaign] as b 
  ON a.[webite] = b.[webite]

Answer 1

您可以将 PIVOT 与 COUNT 一起使用

DECLARE @SampleTable TABLE (website VARCHAR(10), [event type]  VARCHAR(10))
INSERT INTO @SampleTable VALUES
('amazon' ,'imp'),
('amazon' ,'imp'),
('amazon' ,'imp'),
('amazon' ,'click'),
('apple' ,'click'),
('apple' ,'imp'),
('adidas' ,'click'),
('adidas' ,'imp'),
('adidas' ,'click'),
('adidas' ,'imp')


SELECT * FROM @SampleTable
PIVOT (COUNT([event type]) FOR [event type] IN ([imp], [click])) PVT

结果:

website    imp         click
---------- ----------- -----------
adidas     2           2
amazon     3           1
apple      1           1