SQL Server - 动态数据透视

2022-01-22 00:00:00 pivot sql dynamic sql-server

我需要一些帮助.我有两列，地点和名称，我想要每个地点的名称计数.目前数据看起来像:

I'm needing some help. I have two columns, Place and Name, I want a count of Name for each place. Currently the data would look like:

Place | Name
100     OK
100     NEW
100     BAD  
200     BAD
200     BAD
300     OK
400     OK
400     OK
400     OK

我试图让它看起来像，但是我会旋转大约 20 行(这就是为什么我希望命名是动态的

I'm trying to get it to look like, however I would be pivoting about 20 rows (which is why I would like the naming to be dynamic

PLACE | OK | NEW | BAD
100     1     1     1
200     0     0     2
300     1     0     0
400     3     0     0

从其他问题的大杂烩中，这是我想出的，但是，它不起作用.任何帮助将不胜感激.

From a hodge podge of other questions this is what I've come up with, however, it doesn't work. Any help would be greatly appreciated. 

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct 
                        ',' +
                        QUOTENAME(NAMES)
                 FROM INFO_TABLE with (nolock)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = ' SELECT PLACE ,  ' + @cols + ' 

FROM 
(SELECT 
   CASE 
       WHEN NAME IS NOT NULL
       THEN Count(NAME) 
       ELSE 0 END AS EXPR1, 
       PLACE
       FROM INFO_TABLE with (nolock)) t
PIVOT
( 
AVG(Expr1)
FOR NAME IN (' + @cols + ' )
) 
p ' ;

Execute(@query);

推荐答案

您当前的查询已关闭，但由于您想 count names 的数量，您可以使用将其作为聚合函数，而不是在子查询中使用 CASE 语句然后求平均值.

Your current query is close but since you want to count the number of names you can just use that as the aggregate function, instead of using the CASE statement in your subquery and then averaging.

我的建议总是先将查询编写为静态版本，然后再尝试编写动态 SQL 服务器，主要是为了使语法正确:

My suggestion would always be to write the query as a static version first before trying to write a dynamic SQL server, mainly to get the syntax correct:

select place, Bad, New, OK
from
(
  select place, name
  from info_table
) d
pivot
(
  count(name)
  for name in (Bad, New, OK)
) p;

参见 SQL Fiddle with Demo.一旦逻辑正确，您就可以轻松地将其转换为动态 SQL:

See SQL Fiddle with Demo. Once you have the logic correct, then you can easily turn this to dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct 
                        ',' +
                        QUOTENAME(NAME)
                 FROM INFO_TABLE with (nolock)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = ' SELECT PLACE ,  ' + @cols + ' 

FROM 
(
   SELECT NAME, PLACE
   FROM INFO_TABLE with (nolock)
) t
PIVOT
( 
  count(name)
  FOR NAME IN (' + @cols + ' )
) 
p ' ;

Execute(@query);

参见 SQL Fiddle with Demo

相关文章