Using the example from my last post (Cross Tab Queries in SQL Server V2) I was asked to figure out how to make the query more dynamic. The only way to make it truly dynamic (i.e. return a different # of columns) would be to use dynamic SQL. However lets say you want to do a sliding window of 5 years for reporting. . . . Here is an example that utilizes a table valued function. . . . .
CREATE FUNCTION tvfTest
(
@year int
)
RETURNS
@return TABLE
(
ClientID int,
year1 decimal(18, 0),
year2 decimal(18, 0),
year3 decimal(18, 0),
year4 decimal(18, 0),
year5 decimal(18, 0)
)
AS
BEGIN
INSERT INTO @return
SELECT [ClientID], year1 AS year1, year2 AS year2, year3 AS year3, year4 AS year4, year5 AS year5
FROM
(
SELECT [ClientID],
CASE WHEN DATEPART(yy, [SalesDate]) = @year THEN 'year1'
WHEN DATEPART(yy, [SalesDate]) - 1 = @year THEN 'year2'
WHEN DATEPART(yy, [SalesDate]) - 2 = @year THEN 'year3'
WHEN DATEPART(yy, [SalesDate]) - 3 = @year THEN 'year4'
WHEN DATEPART(yy, [SalesDate]) - 4 = @year THEN 'year5'
ELSE 'outofrange' END AS SalesYear,
[SalesAmount]
FROM [Sales]) N
PIVOT
(
SUM ([SalesAmount])
FOR SalesYear IN
(
[year1], [year2], [year3], [year4], [year5] )
) AS pvt
RETURN
END
GO
You then call it like this:
SELECT *
FROM tvfTest(2006)
| ClientID | year1 | year2 | year3 | year4 | year5 |
| 1 | 100 | 200 | 300 | 400 | NULL |
| 2 | 150 | 250 | 250 | 250 | NULL |