CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Ezzy
Ezzy

Reputation: 71

Pivoting Data based on a field and not hard coding the pivot fields

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.

Upvotes: 0

Views: 459

Answers (1)

Martin Navarro
Martin Navarro

Reputation: 624

Unfortunately, with SQL Server, you are not able to create a VIEW for dynamic PIVOT's or for any dynamic SQL at all.

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 the DISTINCT values of your AffectedDate column.
  • The @isNullColumns variable is used to get the column names wrapped around an ISNULL() function. Otherwise your pivoted data could end up with NULL's all over your output.

Upvotes: 2

Related Questions