SQL数据透视表动态 [英] SQL Pivot Table dynamic

问题描述

我已经非常努力地理解如何在SQL中创建数据透视表，但是我无法对其进行管理！

I've tried so hard to understand how to create a pivot table in SQL, but I can't manage it!

我有以下几列:

link_id   route_section   date_1    StartHour     AvJT    data_source
.......   .............  .......   ...........   ......  ............

具有60万行数据.

我在下面的数据透视表中需要它们；

I need them in the following pivot table;

• date_1 StartHour作为列标题
• link_id作为行标题
• AvJT作为数据
• 使用data_source ='1'作为过滤器.

• date_1 StartHour as column headings
• link_id as the row heading
• AvJT as the data
• with data_source = '1' as the filter.

重要表

Link_ID 
date_1      StartHour    00001a    000002a    000003a    000004a
20/01/2014    8           456       4657        556       46576
21/01/2014    8           511       4725        601       52154
22/01/2014    8           468       4587        458       47585
23/01/2014    8           456       4657        556       46576
24/01/2014    8           456       4657        556       46576
25/01/2014    8           456       4657        556       46576
26/01/2014    8           456       4657        556       46576

我设法获得了以下代码，该方法有效，但仅给我date_1作为列标题，而没有给我StartHour额外的要求，或者将date_source ='1'作为过滤器.

I've managed to get the following code, this works but only gives me date_1 as column heading and not StartHour additionally, or with the filter as date_source = '1'.

    Use [C1_20132014]

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Link_ID)
FROM (SELECT DISTINCT Link_ID FROM C1_May_Routes) AS Link_ID

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Date_1, ' + @ColumnName + '
    FROM C1_May_Routes
    PIVOT(SUM(AvJT) 
          FOR Link_ID IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

感谢您的帮助，

亨利

推荐答案

在这里，您将选择一列中的值以显示为数据透视中的列

Here you will select the values in a column to show as column in pivot

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + AvJT + ']', '[' + AvJT + ']')
               FROM    (SELECT DISTINCT AvJT FROM YourTable) PV  
               ORDER BY AvJT

现在旋转查询

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 SELECT date_1, StartHour,AvJT, data_source 
                 FROM YourTable
             ) x
             PIVOT 
             (
                 -- Values in each dynamic column
                 SUM(data_source)
                 FOR AvJT IN (' + @cols + ')                      
            ) p;' 

EXEC SP_EXECUTESQL @query

• 单击此处以查看结果
• Click here to view result

如果要对列名称不是动态的地方进行操作，则可以执行以下查询

If you want to do it to where column names are not dynamic, you can do the below query

SELECT DATE_1,STARTHOUR,
MIN(CASE WHEN AvJT='00001a' THEN data_source END) [00001a],
MIN(CASE WHEN AvJT='00002a' THEN data_source END) [00002a],
MIN(CASE WHEN AvJT='00003a' THEN data_source END) [00003a],
MIN(CASE WHEN AvJT='00004a' THEN data_source END) [00004a]
FROM YOURTABLE
GROUP BY  DATE_1,STARTHOUR

• 单击此处以查看结果
• Click here to view result

我正在更新您的更新问题.

I am updating for your updated question.

声明用于过滤data_source

DECLARE @DATASOURCE VARCHAR(20) = '1' 

代替QUOTENAME，您可以使用另一种格式来获取数据透视表的列

Instead of QUOTENAME, you can use another format to get the columns for pivot

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + Link_ID + ']', '[' + Link_ID + ']')
               FROM    (SELECT DISTINCT Link_ID FROM C1_May_Routes WHERE data_source=@DATASOURCE) PV  
               ORDER BY Link_ID

现在枢轴

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 -- We will select the data that has to be shown for pivoting
                 -- with filtered data_source
                 SELECT date_1, StartHour,AvJT, Link_ID
                 FROM C1_May_Routes
                 WHERE data_source = '+@DATASOURCE+'
             ) x
             PIVOT 
             (
                 -- Values in each dynamic column
                 SUM(AvJT)
                 -- Select columns from @cols 
                 FOR Link_ID IN (' + @cols + ')                      
            ) p;' 

EXEC SP_EXECUTESQL @query

• 单击此处以查看结果
• Click here to view result

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