SQL Server - 动态数据透视 [英] SQL Server - Dynamic Pivot

问题描述

我需要一些帮助。我有两列，地点和名称，我想要每个地方的名字数。目前数据将如下所示：

 地方|名称
 100 OK 
 100新
 100 BAD 
 200 BAD 
 200 BAD 
 300 OK 
 400 OK 
 400 OK 
 400 OK 
  

我试图让它看起来像，但是我会转动大约20行（这就是为什么我想命名为动态

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

从其他问题的一个对话框中，这是我想出来的，但是它不起作用，任何帮助将不胜感激。

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ b（（（（（（（（（（（（（（（（（（（（（（（（（（（（（（（（（（b b b b b b b b b b b b b b b SELECT distinct
'，'+
QUOTENAME（NAMES）
FROM INFO_TABLE with（nolock）
FOR XML PATH（''），TYPE
）.v alue（'。'，'NVARCHAR（MAX）'）
，1,1，''）;

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

FROM
（SELECT
CASE
当名称不是NULL
THEN Count（NAME）
ELSE 0 END AS EXPR1，
PLACE
FROM INFO_TABLE with（nolock））t
PIVOT
（
AVG（Expr1）
FOR NAME IN（'+ @cols +'）
）
p';

执行（@query）;

解决方案

您当前的查询很近， code> count 名称的数量您可以将其用作聚合函数，而不是使用您的子查询中的CASE 语句，然后平均。

我的建议永远是在尝试写入之前先将查询写为静态版本动态SQL服务器，主要是为了得到正确的语法：

 选择地点，坏，新，OK 
从
（
选择地方，名称
 from info_table 
）d 
 pivot 
（
 count（name）
 for name in（Bad ，New，OK）
）p; 
  

请参阅 SQL Fiddle with Demo 。一旦你的逻辑正确，那么你可以很容易地把它转化为动态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'; 
 
执行（@query）; 
  

请参阅 SQL Fiddle with Demo

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

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);

解决方案

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.

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;

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);

See SQL Fiddle with Demo

这篇关于SQL Server - 动态数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！