Please help with dynamic pivot query/ CTE/ SSRS

• alimda

  SSC Journeyman

  Points: 98

  More actions

  November 13, 2013 at 9:56 am

  #100538

  Hi there,

  I'm developing a SSRS report in VS2010 and trying to come up with the following outcome.

  Data1Data2Data3Data420042005…2013

  .. .. .. .. .. ..... ..

  Data 1 through 4 are coming from several CTE joins. The years data (2004 through 2013) supposed to be the most recent 10 years sum of data. I built the years sum data with a static pivot query and joined to my other CTEs via the unique record ID and this report calls one big view that consist of several CTEs and it works fine. As a stated before the years data should have been the most recent 10 years and it is static currently in my report. In other words, I need to change my report next year to reflect the most recent 10 years. I tried to build a dynamic pivot query to replace the static pivot query, however, I couldn't join my dynamic pivot query to my main CTE because the dynamic pivot query declares temp table etc. and I couldn't find a way to join it my main data source. What would be the right approach to build this report?

  Any help is appreciated.

  Thank you,

  Alimda

  ====================================================================

  Current report query is similar to this:

  CTE_Records

  (

  ...

  ),

  CTE_Record_Details

  (

  ...

  ),

  CTE_Pivot --Static Pivot for the most recent 10 years of data

  (

  ...

  )

  SELECT

  ...

  FROM CTE_Records

  JOINS... CTE_Record_Details

  JOINS... CTE_Pivot

  =====================================================================

  I was able to create a dynamic pivot table via this query, but I can't join this to my main query above.

  DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

  DECLARE

  @cols AS NVARCHAR(MAX),

  @y AS INT,

  @sql AS NVARCHAR(MAX);

  -- Construct the column list for the IN clause

  -- e.g., [2002],[2003],[2004]

  SET @cols = STUFF(

  (SELECT N',' + QUOTENAME(y) AS [text()]

  FROM

  (

  SELECT ...

  FROM ...

  ) AS Y

  ORDER BY y

  FOR XML PATH('')),

  1, 1, N'');

  -- Construct the full T-SQL statement

  -- and execute dynamically

  SET @sql = N'SELECT *

  FROM

  (

  SELECT ...

  FROM ...

  ) AS D

  PIVOT(SUM(Amount) FOR [year] IN(' + @cols + N')) AS P;';

  EXEC sp_executesql @sql;