在SQL Server中按两列计数?

Here is an example table:

下面是一个示例表:

CREATE TABLE Example 
(
    LastName varchar(255),
    FirstName varchar(255),
    HomeAddress varchar(255),
    City varchar(255) 
);

INSERT INTO Example VALUES ('Murphy', 'James','123 Easy St', 'New York');
INSERT INTO Example VALUES ('Black', 'John','123 Easy St', 'Boston');
INSERT INTO Example VALUES ('Black', 'Amy','123 Easy St', 'Chicago');
INSERT INTO Example VALUES ('Simpson', 'Bill','123 Easy St', 'New York');
INSERT INTO Example VALUES ('Jones', 'James','123 Easy St', 'Chicago');
INSERT INTO Example VALUES ('Black', 'John','123 Easy St', 'Boston');
INSERT INTO Example VALUES ('Murhpy', 'James','123 Easy St', 'New York');

I want to be able to count by two columns, 'LastName' and 'City'. That is - I want this:

我想要能够数两列，“LastName”和“City”。我想要的是

Name        New York  Boston  Chicago
-------------------------------------
Jones            0      0       1
Black            0      2       1
Simpson          1      0       0
Murphy           2      0       0

My question is similar to this question. I created the following, which produces very close results:

我的问题和这个问题很相似。我创造了以下，产生了非常接近的结果:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames = ISNULL(@PivotColumnNames + ',','') + QUOTENAME(City)
FROM (SELECT DISTINCT City FROM Example) AS cat

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(City) + ', 0) AS '
+ QUOTENAME(City)
FROM (SELECT DISTINCT City FROM Example) AS cat

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT LastName, ' + @PivotSelectColumnNames + '
FROM Example
pivot(count(City) for City in (' + @PivotColumnNames + ')) as pvt';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

BUT, this is not quite what I am after. It produces this:

但是，这并不是我想要的。它产生:

I tried to add 'GROUP BY LastName' to the end of the query, but it does not work. I get an error:

我试图将“GROUP BY LastName”添加到查询的末尾，但它不起作用。我得到一个错误:

Column 'pvt.Boston' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

4 个解决方案

#1

You are so close!

你是如此之近!

Two things:

两件事:

1. Use a subquery/derived table to select only the columns you need for your pivot
2. 使用子查询/派生表只选择主元所需的列
3. Don't misspell Murphy
4. 不要拼错墨菲

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT LastName, ' + @PivotSelectColumnNames + '
FROM (select LastName, City from Example) e
pivot(count(City) for City in (' + @PivotColumnNames + ')) as pvt';

rextester demo: http://rextester.com/ETJ57985

rextester演示:http://rextester.com/ETJ57985

returns:

返回:

+----------+--------+---------+----------+
| LastName | Boston | Chicago | New York |
+----------+--------+---------+----------+
| Black    |      2 |       1 |        0 |
| Jones    |      0 |       1 |        0 |
| Murphy   |      0 |       0 |        2 |
| Simpson  |      0 |       0 |        1 |
+----------+--------+---------+----------+

#2

I think simple pivot will give your query results

我认为简单的pivot将给出您的查询结果。

Select * from (
    Select LastName, City from Example ) a
pivot (count(city) for city in ([New York],[Boston],[Chicago])) p

If you change your query as below you will get appropriate results:

如果你改变你的查询如下，你将得到适当的结果:

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'Select LastName, ' + @PivotSelectColumnNames + ' from ( SELECT LastName, City
    FROM Example ) a
    pivot(count(City) for City in (' + @PivotColumnNames + ')) as pvt';

Output as below:

输出如下:

+----------+----------+--------+---------+
| LastName | New York | Boston | Chicago |
+----------+----------+--------+---------+
| Black    |        0 |      2 |       1 |
| Jones    |        0 |      0 |       1 |
| Murhpy   |        1 |      0 |       0 |
| Murphy   |        1 |      0 |       0 |
| Simpson  |        1 |      0 |       0 |
+----------+----------+--------+---------+

#3

Try this -

试试这个,

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames = ISNULL(@PivotColumnNames + ',','') + QUOTENAME(City)
FROM (SELECT DISTINCT City FROM #example) AS cat

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(City) + ', 0) AS '
+ QUOTENAME(City)
FROM (SELECT DISTINCT City FROM #example) AS cat

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT LastName, ' + @PivotSelectColumnNames + '
FROM #example
pivot(count(City) for City in (' + @PivotColumnNames + ')) as pvt group by LastName, ' + @PivotColumnNames;

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

#4

Alternative approach not using PIVOT:

不使用PIVOT的替代方法:

DECLARE @sql NVARCHAR(max);
SET @sql = ''; -- not necessary for CONCAT, but necessary if converting this answer to + style string concatenation 

SELECT 
  @sql = CONCAT(@sql, ', COUNT(CASE WHEN city = ''', City, ''' THEN 1 END) as ', QUOTENAME(City))
FROM
  Example
GROUP BY City

SET @sql = CONCAT('SELECT Name', @sql, ' FROM example GROUP BY name')

EXEC sp_executesql @sql