SQL Server中两列的动态数据透视 [英] Dynamic pivot on two columns in SQL server

问题描述

我想在sql server表中的两列上创建一个数据库，它必须生成动态列。我试过以下方式



我尝试过：

  CREATE   TABLE  [dbo]。[Test]（
 [Writer] [ nvarchar ]（ 300 ） NULL ，
 [PackageID] [ nvarchar ]（ 300 ） NULL ，
 [PubType] [ nvarchar ]（ 300 ） NULL ，
 [RFT] [ int ]  NULL ，
）
 
  INSERT   INTO 测试
  VALUES  
（'  Ajeeth Kumar'，'  723'，'  ABC'， 1 ），
（'  Ajeeth Kumar'，'  724'，' < span class =code-string> ABC'， 1 ），
（'  Ajeeth Kumar'，'  725'，'  ABC'， 1  ），
（'  Ajeeth Kumar'， '  726'，'  DEF'， 1 ），
（'  Ajeeth Kumar'，'  727'，'  DEF'， 0 ），
（'  Ajeeth Kumar'，'  728'，  DEF'， 0 ）
 
  SELECT  *  FROM 测试
 
  DECLARE   @cols   AS   NVARCHAR （MAX），
  @ query   AS   NVARCHAR （MAX）; 
 
  SET   @cols  = STUFF（（ SELECT   distinct  ' ，'  + QUOTENAME（PubType）
  FROM 测试
  FOR  XML路径（' '）， TYPE  
）.value（' 。'，'  NVARCHAR（MAX）'）， 1 ， 1 ，' '）
 
  PRINT   @cols  
 
  SET  < span class =code-sdkkeywo rd> @ query  = '  SELECT Writer，' +  @cols  + ' 来自
（
 SELECT Writer 
，PackageID 
，PubType 
 FROM测试
）x 
 PIVOT 
（
 COUNT（PackageID）
 for PubType in（' +  @cols  + ' ）
）p 
 WHERE Writer IS NOT NULL' 
 
 
  EXECUTE （ @ query ）

这给了我输出，因为我只使用了PubType列< br $>

作家ABC DEF 
 Ajeeth Kumar 3 3 

但我需要输出与PubType和RFT列一起旋转，以便我的输出看起来像

作家ABC DEF 0 1 
 Ajeeth Kumar 3 3 2 4 

如有任何帮助，我们将不胜感激。谢谢!!

解决方案

您是否考虑过这样的事情：

 SELECT 
 a.Writer 
，SUM（a.ABC）AS ABC 
，SUM（a.DEF）AS DEF 
，SUM（a.RFT_0）AS RFT_0 
，SUM（a.RFT_1）AS RFT_1 
 FROM 
（
 SELECT Writer 
，CASE WHEN PubType ='ABC'THEN 1 ELSE 0 END AS ABC 
 ，例如当PubType ='DEF'那么1 ELSE 0 END AS DEF 
，CASE当RFT = 0那么1 ELSE 0 END AS RFT_0 
，CASE当RFT = 1那么1 ELSE 0结束RFT_1 
 FROM TEST 
）a 
 GROUP BY 
作家

它会给你所需的输出，对我而言比枢轴方法更容易。在例如动态生成此查询c＃不会那么难（虽然我不知道如何在原始sql中执行此操作）

实现这一目标的方法很少。请参考以下内容：

在SQL Server中的两个或多个字段上进行PIVOT - Ken O'Bonn [ ^ ]

sql - TSQL PIVOT MULTIPLE COLUMNS - 堆栈溢出 [ ^ ]

具有多列聚合的SQL Server数据透视表 - 堆栈溢出 [ ^ ]

在多列上进行透视 - SQL Server SQL Ideas [ ^ ]

Hi,

I wanted to make a pivot on two columns in the sql server table which will have to generate dynamic columns. I have tried in the following way

What I have tried:

CREATE TABLE [dbo].[Test](
	[Writer] [nvarchar](300) NULL,
	[PackageID] [nvarchar](300) NULL,
	[PubType] [nvarchar](300) NULL,
	[RFT] [int] NULL,
)

INSERT INTO Test 
VALUES
('Ajeeth Kumar','723','ABC',1),
('Ajeeth Kumar','724','ABC',1),
('Ajeeth Kumar','725','ABC',1),
('Ajeeth Kumar','726','DEF',1),
('Ajeeth Kumar','727','DEF',0),
('Ajeeth Kumar','728','DEF',0)

SELECT * FROM Test

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(PubType)
            FROM Test 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

PRINT @cols 

SET @query = 'SELECT Writer, ' + @cols + ' from 
              (
                SELECT	Writer						
						,PackageID
						,PubType						
                FROM Test
              )x             
              PIVOT 
              (
                COUNT(PackageID)
                for PubType in (' + @cols + ')
              )p              
              WHERE Writer IS NOT NULL'

    
EXECUTE (@query)

This gives me the out put as i had pivoted only with PubType column

Writer	        ABC	DEF
Ajeeth Kumar	3	3

But i need the output to be pivoted with PubType and RFT column as well so that my output looks like

Writer	        ABC	DEF   0   1
Ajeeth Kumar	3	3     2   4

Any help on this will be appreciated. Thank you!!

解决方案

Have you considered doing something like this:

SELECT
  a.Writer
  ,SUM(a.ABC)  AS ABC
  ,SUM(a.DEF)  AS DEF
  ,SUM(a.RFT_0)  AS RFT_0
  ,SUM(a.RFT_1)  AS RFT_1
FROM
(
SELECT  Writer
  ,CASE WHEN PubType = 'ABC' THEN 1 ELSE 0 END AS ABC
  ,CASE WHEN PubType = 'DEF' THEN 1 ELSE 0 END AS DEF
  ,CASE WHEN RFT = 0 THEN 1 ELSE 0 END AS RFT_0
  ,CASE WHEN RFT = 1 THEN 1 ELSE 0 END AS RFT_1
 FROM TEST
 ) a
GROUP BY
 Writer

It will give you the required output and seems easier to me than the pivot approach. Generating this query dynamic in e.g. c# would not be that hard (although I don't know how to do this in raw sql)

There's few ways to achieve that. Please, refer these:
PIVOT on two or more fields in SQL Server – Ken O' Bonn[^]
sql - TSQL PIVOT MULTIPLE COLUMNS - Stack Overflow[^]
SQL Server Pivot Table with multiple column aggregates - Stack Overflow[^]
Pivoting on multiple columns - SQL Server | The SQL Ideas[^]

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