1

I hope this is more specific? sorry if I am unclear, kind of new to this. Thank you for the help!!

I'm trying to get a dynamic pivot to work on a CTE. I have looked around a bit and I have a couple of problems. For what I fount, it seems that something like the following post is pretty standard for a dynamic sql:

Pivot Table and Concatenate Columns

I have the following columns in my table with trades:

Date | product | time | price | volume |

I want to get the average price for each quarter of the day, so I want to pivot the time column after rounding it down to the nearest quarter time. and taking the Weighted average price per product and date.

so I use one CTE to create the pivot list:

DECLARE @pivot_list as varchar(max)

;with startquarter(starttradequarter)
AS
(
SELECT  cast(DATEadd(mi,(datediff(mi,0,Time))/15*15,0)as varchar)
    from [table]
where date > '2014-04-15'
),
PIVOT_CODES(PIVOT_CODE)
AS
(
SELECT DISTINCT starttradequarter AS PIVOT_CODE
                from startquarter
)
SELECT @pivot_list = COALESCE(@pivot_list + ',[' + PIVOT_CODE + ']','[' + PIVOT_CODE + ']')
FROM PIVOT_CODES

then I want to use this variable in a pivot of the table:

;With productselector(Date,startquarter,product,volume,price)
as
(
SELECT [Date]
      ,cast(DATEadd(mi,(datediff(mi,0,Time))/15*15,0)as varchar) as startquarter
      ,[product]
      ,[Volume]
      ,[Price]
FROM [table]
where DelDate = '2014-01-06' and product = 'x'
),
WAPricequarter(startquarter,date,sumvolume,WAPq,product)
AS
(
SELECT startquarter 
      ,Date
      ,sum(volume) as sumvolume
      ,round(sum(volume*price)/sum(volume),2) as WAPq
      ,product
      from productselector
      group by date, startquarter, product
)
SELECT date, product, + @pivot_list
from WAPricequarter
PIVOT (
    SUM([sumvolume])
    FOR startquarter IN (@pivot_list)
) AS pvt

So I see in all dynamic pivots the second statement first put in a variable and then executed, is this necessary?

If not how do I get the pivot to work on the columns in the @pivot_list, it now gives an incorrect syntax error that I can't get to work.

If it is necessary to put it in a variable and then execute, how can I then still filter for product or date inside that variable since I have to use '' around it.

Community
  • 1
  • 1
bramvs
  • 39
  • 1
  • 2
  • 9
  • the variable is needed because if you want a dynamic pivot, then you need dynamic SQL – Lamak Nov 26 '14 at 17:10
  • In order to fully understand the why of dynamic Pivots and CTEs its best to keep in mind the concept of scope and what dynamic SQL really is. The scope of CTE's is pretty contained. Dynamic SQL is really only dynamic is a sense that you are building a query string to have executed. If the query being executed from the dynamic sql doesn't have access to the scope of the object it is trying to query, it will fail – Brad D Nov 26 '14 at 17:56

0 Answers0