ItemKey UpdatedOnHandQty AffectedDate
20406 1594.03 2013-12-27 00:00:00.000
20406 842.132 2014-01-09 00:00:00.000
20406 539.03 2014-02-11 00:00:00.000
20406 486.183 2014-05-12 00:00:00.000
20406 917.86 2014-06-27 00:00:00.000
20406 1314.209 2014-07-29 00:00:00.000
20406 512.261 2014-10-20 00:00:00.000
20406 1394.261 2014-11-12 00:00:00.000
20406 640.19 2014-12-30 00:00:00.000
20406 819.992 2014-08-11 00:00:00.000
I want to pivot this data in sql without having to hard code what the pivot field names have to be. I just want there to be a field name for each unique date (Year, Month, Day). Is there a way to pivot based on this field, as i have 100s of unique Affective Dates. This in the end will be filtered down in a BI tool, but for this report i need to pivot the date fields and tableau does not allow for pivots outside of excel.
我想在sql中透视这些数据,而不必硬编码透视字段名称必须是什么。我只希望每个唯一日期(年,月,日)都有一个字段名称。有没有办法根据这个领域进行调整,因为我有100个独特的情感日期。这最终将在BI工具中过滤掉,但对于此报告,我需要透视日期字段,而tableau不允许在excel之外的枢轴。
1 个解决方案
#1
1
Unfortunately, with SQL Server, you are not able to create a VIEW
for dynamic PIVOT's
or for any dynamic SQL at all.
不幸的是,使用SQL Server,您无法为动态PIVOT或任何动态SQL创建VIEW。
A Stored Procedure can provide you with being able to do this.
存储过程可以使您能够执行此操作。
Code: (Demo)
DECLARE @sql NVARCHAR(MAX)
DECLARE @columns VARCHAR(MAX)
DECLARE @isNullColumns VARCHAR(MAX)
SET @sql = N''
SET @columns = ''
SET @isNullColumns = ''
; WITH item_dates AS
(
SELECT DISTINCT CONVERT(VARCHAR(10), AffectedDate, 112) AS AffectedDate
FROM table1
)
SELECT @columns += N',' + QUOTENAME(AffectedDate)
, @isNullColumns += N',ISNULL(' + QUOTENAME(AffectedDate) + ', 0) AS ' + QUOTENAME(AffectedDate)
FROM item_dates
ORDER BY AffectedDate
SET @columns = STUFF(@columns, 1, 1, '')
SET @isNullColumns = STUFF(@isNullColumns, 1, 1, '')
SET @sql = N'
;WITH cte AS
(
SELECT ItemKey
, CONVERT(VARCHAR(10), AffectedDate, 112) AS AffectedDate
, UpdatedOnHandQty
FROM table1
)
SELECT ItemKey
, ' + @isNullColumns + '
FROM cte
PIVOT
(
SUM(UpdatedOnHandQty) FOR AffectedDate IN (' + @columns + ')
) as p'
EXEC sp_executesql @sql;
Explanation:
- In order to be able to do this dynamically, you need to store the dynamic SQL statement in a variable (ex:
@sql
). - The names of the columns are stored in a variable (
@columns
) in order to build the columns dynamically according to theDISTINCT
values of yourAffectedDate
column. - The
@isNullColumns
variable is used to get the column names wrapped around anISNULL()
function. Otherwise your pivoted data could end up withNULL's
all over your output.
为了能够动态地执行此操作,您需要将动态SQL语句存储在变量中(例如:@sql)。
列的名称存储在变量(@columns)中,以便根据AffectedDate列的DISTINCT值动态构建列。
@isNullColumns变量用于获取包裹在ISNULL()函数周围的列名。否则,您的透视数据最终可能会在输出结束时出现NULL。
#1
1
Unfortunately, with SQL Server, you are not able to create a VIEW
for dynamic PIVOT's
or for any dynamic SQL at all.
不幸的是,使用SQL Server,您无法为动态PIVOT或任何动态SQL创建VIEW。
A Stored Procedure can provide you with being able to do this.
存储过程可以使您能够执行此操作。
Code: (Demo)
DECLARE @sql NVARCHAR(MAX)
DECLARE @columns VARCHAR(MAX)
DECLARE @isNullColumns VARCHAR(MAX)
SET @sql = N''
SET @columns = ''
SET @isNullColumns = ''
; WITH item_dates AS
(
SELECT DISTINCT CONVERT(VARCHAR(10), AffectedDate, 112) AS AffectedDate
FROM table1
)
SELECT @columns += N',' + QUOTENAME(AffectedDate)
, @isNullColumns += N',ISNULL(' + QUOTENAME(AffectedDate) + ', 0) AS ' + QUOTENAME(AffectedDate)
FROM item_dates
ORDER BY AffectedDate
SET @columns = STUFF(@columns, 1, 1, '')
SET @isNullColumns = STUFF(@isNullColumns, 1, 1, '')
SET @sql = N'
;WITH cte AS
(
SELECT ItemKey
, CONVERT(VARCHAR(10), AffectedDate, 112) AS AffectedDate
, UpdatedOnHandQty
FROM table1
)
SELECT ItemKey
, ' + @isNullColumns + '
FROM cte
PIVOT
(
SUM(UpdatedOnHandQty) FOR AffectedDate IN (' + @columns + ')
) as p'
EXEC sp_executesql @sql;
Explanation:
- In order to be able to do this dynamically, you need to store the dynamic SQL statement in a variable (ex:
@sql
). - The names of the columns are stored in a variable (
@columns
) in order to build the columns dynamically according to theDISTINCT
values of yourAffectedDate
column. - The
@isNullColumns
variable is used to get the column names wrapped around anISNULL()
function. Otherwise your pivoted data could end up withNULL's
all over your output.
为了能够动态地执行此操作,您需要将动态SQL语句存储在变量中(例如:@sql)。
列的名称存储在变量(@columns)中,以便根据AffectedDate列的DISTINCT值动态构建列。
@isNullColumns变量用于获取包裹在ISNULL()函数周围的列名。否则,您的透视数据最终可能会在输出结束时出现NULL。