Hi Everyone,

I was trying to create a Common Table Express to store some data that  I need  a bunch of inner join.  Then I would like to pivot the result using dynamic pivot columns.  I wrote the below query but I'm getting the error " Common table expression defined but not used."  How can I create a pivot query base on a CTE?   BTW...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)

• Edited by zXSwordXz 8 hours 0 minutes ago

You can use a temp table to your CTE and use the temp table in your dynamic pivot.

Try:

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
into temptable	

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 temptable	) as T
    PIVOT 
        (
            Max(Total)
            for [CostCenterNumber] in (' + @cols + ')
        )) as P'
EXEC(@sql)

drop table temptable

You can do it, but you have to put the cte inside the statement contained in @sql.

DECLARE @cols nvarchar(max)
DECLARE @sql nvarchar(max)
SELECT @cols = (Select Stuff(
    (Select ',[ ' + Cast(CostCenterNumber As varchar(12)) + ']' From CostCenters Order By CostCenterNumber 
	For XML Path(''),Type)
    .value('text()[1]','nvarchar(max)'),1,1,N''))

   SELECT @sql = '
;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 *
    From(
    SELECT [Year], CostCenterNumber, GLClass, Code, GLDescription, Total
    FROM PivotData) as T
    PIVOT 
        (
            Max(Total)
            for [CostCenterNumber] in (' + @cols + ')
        ) as P'

EXEC(@sql)

Note, your original sql had one too many ) in it, so I changed the last line )) as P' to ) as P'.  I also changed the statement building the @cols to the safer FOR XML Path() menhod.

Tom

• Proposed as answer by Avijit Swain 5 hours 30 minutes ago