This SQL is to demonstrate a dynamic pivot query. It is dynamic because the number of columns is not known in advance. Or because it is known but the number of columns is large but incremental. In this sample the pivot column is week of year.
SELECT * INTO #DATA FROM (SELECT
CAST('Women' as varchar(50)) as UserName
, CAST('1/1/2014' As datetime) As DateDied
, CAST(48 As int) As Age
, CAST(5 As int) As Dollars
, CAST(1 As int) As Heirs) X
INSERT INTO #DATA SELECT 'Men', '1/5/2014', 50, 300, 2
INSERT INTO #DATA SELECT 'Men', '1/20/2014', 55, 5000, 4
INSERT INTO #DATA SELECT 'Women', '2/20/2014', 23, 55, 0
INSERT INTO #DATA SELECT 'Women', '2/20/2014', 43, 22, 1
--this part will blow out the number of @days in the past and future of @date
DECLARE @date datetime = CAST(GetDate() as date)
DECLARE @days int = 365
;WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTEFINAL AS (SELECT TOP (@days) CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as int) AS RowNum FROM CTE4 x, CTE4 y)
SELECT
Offset,
DateRangeDate As [Date]
, DatePart(yyyy,DateRangeDate) As [Year]
, DatePart(mm,DateRangeDate) As [Month]
, DateName(mm,DateRangeDate) As [MonthName]
, DatePart(dd,DateRangeDate) As [Day]
, DatePart(dy,DateRangeDate) As [DayOfYear]
, DatePart(wk,DateRangeDate) As [Week]
, DatePart(dw,DateRangeDate) As [DayOfWeek]
, DateName(dw,DateRangeDate) As [DayName]
, CASE WHEN DatePart(mm,DateRangeDate) IN (1,2,3)
THEN 1 WHEN DatePart(mm,DateRangeDate) IN (4,5,6)
THEN 2 WHEN DatePart(mm,DateRangeDate) IN (7,8,9)
THEN 3 ELSE 4 END As [Quarter]
INTO #DATEFACTS FROM
(SELECT RowNum -1 As Offset, @date + RowNum -1 AS DateRangeDate FROM CTEFINAL
UNION
SELECT -RowNum As Offset, @date - RowNum AS DateRangeDate FROM CTEFINAL) X
--Now you have some date facts
--This is your query
SELECT td.[Week], td.[Month], td.[Quarter], td.[Year], f.UserName, f.DateDied, f.Age, f.Dollars, f.Heirs
INTO #TEMP FROM #DATA f WITH (NOLOCK)
INNER JOIN #DATEFACTS td ON td.[Date] = f.DateDied
--Edit Defaults Below
DECLARE @col2prefix varchar(50) = 'A'
DECLARE @col3prefix varchar(50) = 'D'
DECLARE @col4prefix varchar(50) = 'H'
DECLARE @col2label varchar(50) = 'Avg Age Week'
DECLARE @col3label varchar(50) = 'Sum Dollars Week'
DECLARE @col4label varchar(50) = 'Avg Heirs Week'
DECLARE @col2data varchar(50) = 'Age'
DECLARE @col3data varchar(50) = 'Dollars'
DECLARE @col4data varchar(50) = 'Heirs'
DECLARE @order varchar(50) = @col2label
DECLARE @sort varchar(50) = 'DESC'
DECLARE @grouprow varchar(50) = 'UserName'
--Edit Defaults Above
declare @col1 varchar(MAX), @col2 varchar(MAX), @col3 varchar(MAX), @col4 varchar(MAX), @colorder varchar(MAX)
declare @sql varchar(MAX)
-- Replace all [Week] below with your pivot column value
;WITH CTE AS (SELECT DISTINCT [Week] FROM #TEMP)
select @col1 = COALESCE(@col1 + ', ','')
+ 'MAX(' + @col2prefix + CAST([Week] as varchar(100)) + ') AS ' + QUOTENAME(@col2label + ' ' + CAST([Week] as varchar(100)))
+ ',MAX(' + @col3prefix + CAST([Week] as varchar(100)) + ') AS ' + QUOTENAME(@col3label + ' ' + CAST([Week] as varchar(100)))
+ ',MAX(' + @col4prefix + CAST([Week] as varchar(100)) + ') AS ' + QUOTENAME(@col4label + ' ' + CAST([Week] as varchar(100)))
,@col2 = COALESCE(@col2 + ', ','') + @col2prefix + CAST([Week] as varchar(100))
,@col3 = COALESCE(@col3 + ', ','') + @col3prefix + CAST([Week] as varchar(100))
,@col4 = COALESCE(@col4 + ', ','') + @col4prefix + CAST([Week] as varchar(100))
,@colorder = COALESCE(@colorder + ', ','') + '[' + @order + ' ' + CAST([Week] as varchar(10)) + '] ' + @sort
FROM cte ORDER BY [Week]
SET @sql = 'SELECT ' + @grouprow + ', ' + @col1 + 'FROM ( SELECT ' + @grouprow + ',' + @col2 + ',' + @col3 + ',' + @col4 + ' FROM ('
+ 'SELECT ' + @grouprow + '
, ''' + @col2prefix + ''' + CAST([Week] as varchar(100)) As ' + @col2prefix + '
, ''' + @col3prefix + ''' + CAST([Week] as varchar(100)) As ' + @col3prefix + '
, ''' + @col4prefix + ''' + CAST([Week] as varchar(100)) As ' + @col4prefix + '
, ' + @col2data + ', ' + @col3data + ', ' + @col4data + ' FROM #TEMP R
) R2
PIVOT ( AVG(' + @col2data + ') FOR ' + @col2prefix + ' IN (' + @col2 + ')) AS P2
PIVOT ( SUM(' + @col3data + ') FOR ' + @col3prefix + ' IN (' + @col3 + ')) AS P3
PIVOT ( AVG(' + @col4data + ') FOR ' + @col4prefix + ' IN (' + @col4 + ')) AS P4
) AS X
GROUP BY ' + @grouprow + ' ORDER BY ' + @colorder
EXEC (@sql);
DROP TABLE #TEMP
DROP TABLE #DATEFACTS
DROP TABLE #DATA