none
Please help with dynamic pivot query/ CTE/ SSRS

    Question

  • Hi there,

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

    Data1 Data2 Data3 Data4 2004 2005 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 currrently 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;
    • Moved by Kalman Toth Wednesday, November 13, 2013 10:13 PM SSRS better fit
    Wednesday, November 13, 2013 4:50 PM

Answers

  • Try the following:

    DECLARE @StartDate DATETIME
    	,@EndDate DATETIME
    
    SET @StartDate = dateadd(year, - 10 + datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101');
    SET @EndDate = dateadd(year, 11, @StartDate);
    
    DECLARE @Columns NVARCHAR(max)
    	,@Year INT;
    
    SET @Columns = '';
    SET @Year = datepart(year, CURRENT_TIMESTAMP) - 10;
    
    WHILE @year <= datepart(year, CURRENT_TIMESTAMP)
    BEGIN
    	SET @Columns = @Columns + ', ' + quotename(cast(@year AS NVARCHAR(max))) 
    	SET @year = @year + 1;
    END
    
    SET @Columns = STUFF(@Columns, 1, 2, '');
    SELECT @Columns;
    
    declare @SQL nvarchar(max);
    
    SET @SQL = ';WITH CTE AS (SELECT R.[ID], R.[FirstName], R.[LastName], RD.Address, RD.Phone
    FROM dbo._Records R LEFT JOIN dbo._RecordDetails RD on R.ID = RD.RecordID),
    cte2 AS 
    (SELECT cte.ID, cte.FirstName, cte.LastName, cte.Address, cte.Phone, M.RecordID, datepart(year,M.Date) as yDate, M.Amount
    FROM CTE INNER JOIN dbo._Money M ON cte.ID = M.RecordID
    WHERE M.Date >=@StartDate and M.Date < @EndDate)
    
    SELECT * FROM cte2 PIVOT (SUM(Amount) FOR yDate IN (' + @Columns + ')) pvt'
    
    execute sp_ExecuteSQL @SQL, N'@StartDate datetime, @EndDate datetime', @StartDate, @EndDate 
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by alimda Sunday, November 17, 2013 5:23 AM
    Thursday, November 14, 2013 3:15 AM

All replies

  • Please post DDL+DML

    [Personal Site] [Blog] [Facebook]signature

    Wednesday, November 13, 2013 4:58 PM
  • Hi pituach, thank you for your quick response. I have my query samples at the end of my post. Are they helpful or do you need more details?

    Thank you.

    alimda

    Wednesday, November 13, 2013 5:02 PM
  • We need DDL+DML (a query for creating the elements like tables, and query to insert some sample data) in order to check your query and write our own.

    * SQL statements are divided into two major categories: data definition language (DDL) and data manipulation language (DML). DDL statements are used to build and modify the structure of objects (CReate/Drop...). DML statements are used to work with the data (insert/delete/update...).


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituach Wednesday, November 13, 2013 7:09 PM
    Wednesday, November 13, 2013 7:04 PM
  • Thank you pituach,

    DDL:

    CREATE TABLE [dbo].[_Records](
    [ID] [varchar](255) NULL,
    [FirstName] [varchar](255) NULL,
    [LastName] [varchar](255) NULL
    ) ON [PRIMARY]

    GO

    INSERT INTO [dbo].[_Records]
               ([ID], [FirstName], [LastName])
         VALUES
    ('1', 'A1', 'B1'),
    ('2', 'A2', 'B2'),
    ('3', 'A3', 'B3'),
    ('4', 'A4', 'B4'),
    ('5', 'A5', 'B5')
    GO

    CREATE TABLE [dbo].[_RecordDetails](
    [RecordID] [varchar](255) NULL,
    [Address] [varchar](255) NULL,
    [Phone] [varchar](255) NULL
    ) ON [PRIMARY]

    GO

    INSERT INTO [dbo].[_RecordDetails]
               ([RecordID]
               ,[Address]
               ,[Phone])
         VALUES
    ('1', 'Add1', 'P1'),
    ('2', 'Add2', 'P2'),
    ('3', 'Add3', 'P3'),
    ('4', 'Add4', 'P4'),
    ('5', 'Add5', 'P5')
    GO

    CREATE TABLE [dbo].[_Money](
    [RecordID] [varchar](255) NULL,
    [Date] [varchar](255) NULL,
    [Amount] [numeric](20, 4) NOT NULL,
    ) ON [PRIMARY]

    GO

    INSERT INTO [dbo].[_Money]
               ([RecordID]
               ,[Date]
               ,[Amount])
         VALUES
    ('1', '1/1/2004', '5'),
    ('1', '2/1/2004', '10'),
    ('1', '4/1/2006', '4'),
    ('1', '6/1/2007', '6'),
    ('1', '3/1/2010', '8'),
    ('2', '3/1/2004', '4'),
    ('2', '4/1/2004', '6'),
    ('2', '5/1/2005', '7'),
    ('2', '6/1/2011', '8'),
    ('3', '1/1/2005', '5'),
    ('3', '2/1/2005', '10'),
    ('3', '3/1/2007', '4'),
    ('3', '4/1/2008', '6'),
    ('3', '5/1/2008', '8'),
    ('3', '6/1/2009', '4'),
    ('3', '7/1/2012', '6'),
    ('3', '8/1/2012', '7'),
    ('3', '9/1/2012', '8'),
    ('4', '1/1/2006', '5'),
    ('4', '2/1/2006', '10'),
    ('4', '3/1/2008', '4'),
    ('4', '4/1/2008', '6'),
    ('4', '5/1/2008', '8'),
    ('4', '6/1/2010', '4'),
    ('4', '7/1/2011', '6'),
    ('4', '8/1/2011', '7'),
    ('4', '9/1/2011', '8'),
    ('4', '10/1/2012', '5'),
    ('4', '11/1/2012', '10'),
    ('4', '7/1/2013', '4'),
    ('4', '8/1/2013', '6'),
    ('4', '9/1/2013', '8'),
    ('5', '4/1/2008', '4'),
    ('5', '6/1/2010', '6'),
    ('5', '6/1/2011', '7'),
    ('5', '7/1/2011', '8'),
    ('5', '8/1/2011', '5'),
    ('5', '9/1/2012', '10'),
    ('5', '10/1/2012', '4'),
    ('5', '11/1/2013', '6'),
    ('5', '7/1/2013', '8'),
    ('5', '8/1/2013', '4'),
    ('5', '9/1/2013', '6'),
    ('5', '10/1/2013', '7'),
    ('5', '11/1/2013', '8')
    GO

    Wednesday, November 13, 2013 8:31 PM
  • This is my simple query with a static Pivot. How can make the pivot query dynamic that works with the other CTEs?

    WITH

    CTE_Records AS
    (
    SELECT *
    FROM _Records
    ),

    CTE_RecordDetails AS
    (
    SELECT *
    FROM _RecordDetails
    ),

    CTE_Pivot AS
    (
    SELECT RecordID, [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013]
    FROM
    (
    SELECT RecordID,
    YEAR ([Date]) AS [year],
    Amount
    FROM _Money
    WHERE YEAR([Date])>=(YEAR(GETDATE())-10)
    AND YEAR([Date])<=YEAR(GETDATE())
    ) AS SOURCE
    PIVOT
    (
    SUM (Amount)
    FOR [year] IN ([2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013])
    ) AS  Pvt
    )

    SELECT *
    FROM CTE_Records R
    LEFT OUTER JOIN CTE_RecordDetails RD
    ON R.ID=RD.RecordID
    LEFT OUTER JOIN CTE_Pivot P
    ON R.ID=P.RecordID


    Wednesday, November 13, 2013 8:33 PM
  • SSRS has built-in dynamic columns (crosstab), you don't have to do it in T-SQL.

    I am moving it to SSRS.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Edited by Kalman Toth Wednesday, November 13, 2013 10:12 PM
    Wednesday, November 13, 2013 8:36 PM
  • I was able to create a dynamic pivot with this query, but I can't join this piece to my main CTE. Any help is appreciated.

    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 DISTINCT YEAR ([Date]) AS y
    FROM _Money
    WHERE YEAR([Date])>=(YEAR(GETDATE())-10)
    AND YEAR([Date])<=YEAR(GETDATE())
    ) 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 RecordID,
    YEAR ([Date]) AS [year],
    Amount
    FROM _Money
    WHERE YEAR([Date])>=(YEAR(GETDATE())-10)
    AND YEAR([Date])<=YEAR(GETDATE())
    ) AS D
    PIVOT(SUM(Amount) FOR [year] IN(' + @cols + N')) AS P;';

    EXEC sp_executesql @sql;


    Wednesday, November 13, 2013 8:38 PM
  • Thank you, Kalman.

    How should I develop my main query in that case? Could you please provide a simple code?

    I can create a simple matrix table (pivot) in SSRS just for the money portion, but how can I join it to the main tabular table that has the record details?

    The report should look like this:

    Data1 Data2 Data3 Data4 2004 2005 2013
     ..  ..  ..  ..  ..  .. ...   ..

    Wednesday, November 13, 2013 8:44 PM
  • You actually did all the hard work :-)

    just add in the begining 

    select * from (

    and in the end remove the ";" and add

    ) T 
    left join _Records on  _Records.ID = T.RecordID


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, November 13, 2013 8:52 PM
  • here this your query with the missing data from table _Records

    I hope i understood what you need :-)

    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  DISTINCT YEAR ([Date]) AS y
    FROM _Money
    WHERE YEAR([Date])>=(YEAR(GETDATE())-10)
    AND YEAR([Date])<=YEAR(GETDATE())
    ) 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
    (
    SELECT  RecordID,
    YEAR ([Date]) AS [year],
    Amount
    FROM _Money
    WHERE YEAR([Date])>=(YEAR(GETDATE())-10)
    AND YEAR([Date])<=YEAR(GETDATE())
    ) AS D
    PIVOT(SUM(Amount) FOR [year] IN(' + @cols + N')) AS P
    ) T 
    left join _Records on  _Records.ID = T.RecordID';
    print @sql
    EXEC sp_executesql @sql;


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, November 13, 2013 8:55 PM
  • or use this (just remove duplicate ID column from results)

    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  DISTINCT YEAR ([Date]) AS y
    FROM _Money
    WHERE YEAR([Date])>=(YEAR(GETDATE())-10)
    AND YEAR([Date])<=YEAR(GETDATE())
    ) AS Y
    ORDER BY y
    FOR XML PATH('')),
    1, 1, N'');
    
    -- Construct the full T-SQL statement
    -- and execute dynamically
    SET @sql = N'
    select _Records.[FirstName], _Records.[LastName] , T.* from (
    SELECT *
    FROM
    (
    SELECT  RecordID,
    YEAR ([Date]) AS [year],
    Amount
    FROM _Money
    WHERE YEAR([Date])>=(YEAR(GETDATE())-10)
    AND YEAR([Date])<=YEAR(GETDATE())
    ) AS D
    PIVOT(SUM(Amount) FOR [year] IN(' + @cols + N')) AS P
    ) T 
    left join _Records on  _Records.ID = T.RecordID';
    print @sql
    EXEC sp_executesql @sql;

    Is this what you need?
    * If no so hope someone else will help or wait for tomorrow (23:04 in Israel now... i go to sleep)


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituach Wednesday, November 13, 2013 9:04 PM
    Wednesday, November 13, 2013 9:02 PM
  • Hi Pituach,

    We're getting close :)

    I see you have joined just the table to the dynamic pivot, but I have 4 complicated CTEs that need to be joined to this dynamic pivot. In my example above, I wrote a simple CTE that calls data from just one table so that you can see what I'm trying to achieve. 

    I have 4 complicated CTEs that need to be joined with this dynamic pivot. Have you checked my simple CTEs above that gets the record and record details from different tables, so imagine that these are complicated queries that I build CTEs for them and one of them is the static pivot. At the end of the day, I want to build a stored proc out of these CTEs and the dynamic pivot and call the stored proc from SSRS.

    Is this doable?

    Thank you again.

    Wednesday, November 13, 2013 9:51 PM
  • To me this looks like you need to use a stored procedure to generate this report. In that stored procedure you will use dynamic pivot.

    How does it sound?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, November 13, 2013 9:58 PM
  • I didn't follow completely, but in my opinion you can include these CTE before your dynamic query and join inside that dynamic query. E.g. your dynamic query can have a static part and dynamic part. Or just select stuff into temp table and then use that temp table in your dynamic query, it will see this temp table if it was created prior to that dynamic query execution.

    In any case, this complex logic cries for stored procedure.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, November 13, 2013 10:01 PM
  • Hi Naomi,

    That sounds good, but how should I code the stored proc that gives me the desired outcome?

    Could you please provide me with a sample stored proc that combines the CTEs and the dynamic pivot above?

    Thank you,

    Wednesday, November 13, 2013 10:02 PM
  • Can you simply include your 4 cte into your dynamic SQL code?

    Can you post one more time what you have and how did you plan to JOIN the things? I think you can just include your 4 cte into the same SQL script prior to the dynamic part and then join, right?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, November 13, 2013 10:10 PM
  • This is my main query with static pivot. I want to make the static pivot part dynamic:

    WITH
    
    CTE_Records AS
    (
    SELECT	*
    FROM	_Records
    ),
    
    CTE_RecordDetails AS
    (
    SELECT	*
    FROM	_RecordDetails
    ),
    
    CTE_Pivot AS
    (
    SELECT	RecordID, [2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013]
    FROM
    		(
    		SELECT	RecordID,
    				YEAR ([Date]) AS [year],
    				Amount
    		FROM	_Money
    		WHERE	YEAR([Date])>=(YEAR(GETDATE())-10)
    				AND YEAR([Date])<=YEAR(GETDATE())
    		) AS SOURCE
    		PIVOT
    		(
    		SUM (Amount)
    		FOR [year] IN ([2003], [2004], [2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012], [2013])
    		) AS  Pvt
    )
    
    SELECT	*
    FROM	CTE_Records R
    		LEFT OUTER JOIN CTE_RecordDetails RD
    			ON R.ID=RD.RecordID
    		LEFT OUTER JOIN CTE_Pivot P
    			ON R.ID=P.RecordID

    This is the stand alone dynamic pivot that should replace the static pivot:

    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  DISTINCT YEAR ([Date]) AS y
    FROM _Money
    WHERE YEAR([Date])>=(YEAR(GETDATE())-10)
    AND YEAR([Date])<=YEAR(GETDATE())
    ) 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  RecordID,
    YEAR ([Date]) AS [year],
    Amount
    FROM _Money
    WHERE YEAR([Date])>=(YEAR(GETDATE())-10)
    AND YEAR([Date])<=YEAR(GETDATE())
    ) AS D
    PIVOT(SUM(Amount) FOR [year] IN(' + @cols + N')) AS P;';
    
    EXEC sp_executesql @sql;

    can I create one single stored proc that will give me the following outcome?

    Data1 Data2 Data3 Data4 2004 2005 2013
     ..  ..  ..  ..  ..  .. ...   ..

    Wednesday, November 13, 2013 10:15 PM
  • I want to be able to call one stored proc that gives me desired outcome. I think I'm so close, but I couldn't put the pieces together :)

    Thank you.

    Wednesday, November 13, 2013 10:18 PM
  • Here are the simple DDL and DML to create sample tables.

    CREATE TABLE [dbo].[_Records](
    	[ID] [varchar](255) NULL,
    	[FirstName] [varchar](255) NULL,
    	[LastName] [varchar](255) NULL
    ) ON [PRIMARY]
    
    GO
    
    INSERT INTO [dbo].[_Records]
               ([ID], [FirstName], [LastName])
         VALUES
    		('1', 'A1', 'B1'),
    		('2', 'A2', 'B2'),
    		('3', 'A3', 'B3'),
    		('4', 'A4', 'B4'),
    		('5', 'A5', 'B5')
    GO
    
    CREATE TABLE [dbo].[_RecordDetails](
    	[RecordID] [varchar](255) NULL,
    	[Address] [varchar](255) NULL,
    	[Phone] [varchar](255) NULL
    ) ON [PRIMARY]
    
    GO
    
    INSERT INTO [dbo].[_RecordDetails]
               ([RecordID]
               ,[Address]
               ,[Phone])
         VALUES
    		('1', 'Add1', 'P1'),
    		('2', 'Add2', 'P2'),
    		('3', 'Add3', 'P3'),
    		('4', 'Add4', 'P4'),
    		('5', 'Add5', 'P5')
    GO
    
    CREATE TABLE [dbo].[_Money](
    	[RecordID] [varchar](255) NULL,
    	[Date] [varchar](255) NULL,
    	[Amount] [numeric](20, 4) NOT NULL,
    ) ON [PRIMARY]
    
    GO
    
    INSERT INTO [dbo].[_Money]
               ([RecordID]
               ,[Date]
               ,[Amount])
         VALUES
    		('1', '1/1/2004', '5'),
    		('1', '2/1/2004', '10'),
    		('1', '4/1/2006', '4'),
    		('1', '6/1/2007', '6'),
    		('1', '3/1/2010', '8'),
    		('2', '3/1/2004', '4'),
    		('2', '4/1/2004', '6'),
    		('2', '5/1/2005', '7'),
    		('2', '6/1/2011', '8'),
    		('3', '1/1/2005', '5'),
    		('3', '2/1/2005', '10'),
    		('3', '3/1/2007', '4'),
    		('3', '4/1/2008', '6'),
    		('3', '5/1/2008', '8'),
    		('3', '6/1/2009', '4'),
    		('3', '7/1/2012', '6'),
    		('3', '8/1/2012', '7'),
    		('3', '9/1/2012', '8'),
    		('4', '1/1/2006', '5'),
    		('4', '2/1/2006', '10'),
    		('4', '3/1/2008', '4'),
    		('4', '4/1/2008', '6'),
    		('4', '5/1/2008', '8'),
    		('4', '6/1/2010', '4'),
    		('4', '7/1/2011', '6'),
    		('4', '8/1/2011', '7'),
    		('4', '9/1/2011', '8'),
    		('4', '10/1/2012', '5'),
    		('4', '11/1/2012', '10'),
    		('4', '7/1/2013', '4'),
    		('4', '8/1/2013', '6'),
    		('4', '9/1/2013', '8'),
    		('5', '4/1/2008', '4'),
    		('5', '6/1/2010', '6'),
    		('5', '6/1/2011', '7'),
    		('5', '7/1/2011', '8'),
    		('5', '8/1/2011', '5'),
    		('5', '9/1/2012', '10'),
    		('5', '10/1/2012', '4'),
    		('5', '11/1/2013', '6'),
    		('5', '7/1/2013', '8'),
    		('5', '8/1/2013', '4'),
    		('5', '9/1/2013', '6'),
    		('5', '10/1/2013', '7'),
    		('5', '11/1/2013', '8')
    GO


    • Edited by alimda Wednesday, November 13, 2013 10:41 PM
    Wednesday, November 13, 2013 10:41 PM
  • Here is the result that I'm trying to achieve. And then I'm going to call this stored proc in SSRS report and display all these columns...

    Wednesday, November 13, 2013 10:44 PM
  • Try the following:

    DECLARE @StartDate DATETIME
    	,@EndDate DATETIME
    
    SET @StartDate = dateadd(year, - 10 + datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101');
    SET @EndDate = dateadd(year, 11, @StartDate);
    
    DECLARE @Columns NVARCHAR(max)
    	,@Year INT;
    
    SET @Columns = '';
    SET @Year = datepart(year, CURRENT_TIMESTAMP) - 10;
    
    WHILE @year <= datepart(year, CURRENT_TIMESTAMP)
    BEGIN
    	SET @Columns = @Columns + ', ' + quotename(cast(@year AS NVARCHAR(max))) 
    	SET @year = @year + 1;
    END
    
    SET @Columns = STUFF(@Columns, 1, 2, '');
    SELECT @Columns;
    
    declare @SQL nvarchar(max);
    
    SET @SQL = ';WITH CTE AS (SELECT R.[ID], R.[FirstName], R.[LastName], RD.Address, RD.Phone
    FROM dbo._Records R LEFT JOIN dbo._RecordDetails RD on R.ID = RD.RecordID),
    cte2 AS 
    (SELECT cte.ID, cte.FirstName, cte.LastName, cte.Address, cte.Phone, M.RecordID, datepart(year,M.Date) as yDate, M.Amount
    FROM CTE INNER JOIN dbo._Money M ON cte.ID = M.RecordID
    WHERE M.Date >=@StartDate and M.Date < @EndDate)
    
    SELECT * FROM cte2 PIVOT (SUM(Amount) FOR yDate IN (' + @Columns + ')) pvt'
    
    execute sp_ExecuteSQL @SQL, N'@StartDate datetime, @EndDate datetime', @StartDate, @EndDate 
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by alimda Sunday, November 17, 2013 5:23 AM
    Thursday, November 14, 2013 3:15 AM
  • Did you see my answer?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, November 17, 2013 5:21 AM
  • Hi Naomi,

    I worked on implementing your query structure to my main query. Yes, it did work fine with little changes on it even though my main query is a complicated one. I liked your most recent 10 year looping method. Knowing that the money table will have data for each each sure, there is no need to select distinct that table..

    Sorry for being late with my response, I had some other fires going...

    Thank you so much again.

    Have a great evening.

    Sunday, November 17, 2013 5:32 AM