zXSwordXz zXSwordXz - 4 months ago 12
SQL Question

Dynamic Pivot with CTE

I was trying to create a Common Table Express(CTE) to store some data that I need which requires a bunch of inner join. Then, I would like to pivot the result using dynamic pivot columns. I wrote the query below but I'm getting the error


"Common table expression defined but not used."


How can I create a pivot query base on a CTE? By the way, I can do it without the CTE but I would like to know if I can do it with CTE.

DECLARE @cols nvarchar(max)
DECLARE @sql nvarchar(max)
SELECT @cols = isnull(@cols + ', ', '') + '[' + Convert(varchar(max),T.CostCenterNumber) + ']' FROM (SELECT distinct CostCenterNumber FROM CostCenters) as T
;With PivotData as (
SELECT B.[Year], C.CostCenterNumber, C.CostCenterName, E.[Description] as GLClass, D.Code, D.[GLDescription], A.Total
From GeneralLedgers A inner join
Years B on A.YearID = B.ID
inner join CostCenters C on
A.CostCenterID = C.ID
inner join GLCodes D on
A.GLCodeID = D.ID inner join
GLClassificationTypes E on
D.GLClassificationTypeID = E.ID)


SELECT @sql = '
Select *
From(
SELECT [Year], CostCenterNumber, GLClass, Code, GLDescription, Total
FROM PivotData) as T
PIVOT
(
Max(Total)
for [CostCenterNumber] in (' + @cols + ')
)) as P'
EXEC(@sql)

Answer

Here is how you can do it. I have written the logic inside the query

DECLARE
@cols nvarchar(max),
@stmt nvarchar(max)
SELECT @cols = isnull(@cols + ', ', '') + '[' + Convert(nvarchar(max),T.CostCenterNumber)+ ']' 
               FROM (SELECT distinct CostCenterNumber FROM CostCenters) as T

SELECT @stmt = '
-- Your CTE goes here
;WITH CTE AS
(
      SELECT [Year], E.[Description] as GLClass, Code, 
      GLDescription,  CostCenterNumber, Total
      FROM GeneralLedgers A inner join
      Years B on A.YearID = B.ID inner join
      GLCodes C on A.GLCodeID = C.ID inner join
      CostCenters D on A.CostCenterID = D.ID inner join
      GLClassificationTypes E on C.GLClassificationTypeID = E.ID
)
-- Pivoted reuslt
SELECT * FROM
(  
     -- Here you select the data from CTE
     SELECT * 
     FROM CTE
)as T
PIVOT 
(
     max(T.Total)
     for T.[CostCenterNumber] in (' + @cols + ')
) as P'
exec sp_executesql  @stmt = @stmt
Comments