I have a question relating to round values from dynamic columns.
I am dynamically (Over past X days) creating a list of player with their daily score and I would like to round these scores with this kind of format ROUND(Score, 2). Here is my query:
DECLARE @Column AS NVARCHAR(MAX),
@SQL AS NVARCHAR(MAX)
SELECT [Player], [Start], [Average]
INTO #DbTemp1
FROM [PlayerScoreByWeek]
WHERE [Start] >= DateAdd(DAY, -7, GETDATE())
SELECT @Column = STUFF((SELECT ',' + QUOTENAME([Start])
FROM #DbTemp1
GROUP BY [Start]
ORDER BY [Start] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @SQL = 'SELECT [Player],' + @Column + '
INTO #DbTemp2
FROM
(
SELECT [Player], [Start], [Average]
FROM #DbTemp1
) AS SourceTable
PIVOT
(
AVG([Average])
FOR [Start] IN (' + @Column + ')
) AS PivotTable
ORDER BY Len([Player]), Player
SELECT * FROM #DbTemp2'
EXECUTE(@SQL)
The current output is as follows:
Player 2014-02-24 2014-02-25 2014-02-26 2014-02-27 2014-02-28 2014-03-01 2014-03-02
------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
Carl 26.21654 55.1542 12.29612 29.16546 32.4556 21.1485 48.3652
Gene 46.13444 29.3298 16.31642 12.86592 65.6235 52.8525 31.5915
Kim 25.69554 39.5986 32.23184 34.55685 59.2125 12.2316 29.1242
And this is the desired output:
Player 2014-02-24 2014-02-25 2014-02-26 2014-02-27 2014-02-28 2014-03-01 2014-03-02
------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
Carl 26.22 55.15 12.30 29.17 32.46 21.14 48.37
Gene 46.13 29.33 16.32 12.87 65.62 52.85 31.59
Kim 25.70 39.60 32.23 34.56 59.21 12.23 29.12
Given that the columns are created dynamically it is not possible to use the round function inside the '@SQL' query. Thanks for your help !
Create another variable for your outputs - eg:
DECLARE @Column AS NVARCHAR(MAX), @Output nvarchar(max), @SQL AS NVARCHAR(MAX)
SELECT [Player], [Start], [Average] INTO #DbTemp1 FROM [PlayerScoreByWeek]
WHERE [Start] >= DateAdd(DAY, -7, GETDATE())
SELECT @Column = STUFF((SELECT ','+ QUOTENAME([Start])
FROM #DbTemp1
GROUP BY [Start]
ORDER BY [Start] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''),
@Output = STUFF((SELECT ', round('+ QUOTENAME([Start])+',2) as ' + QUOTENAME([Start])
FROM #DbTemp1
GROUP BY [Start]
ORDER BY [Start] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @SQL = 'SELECT [Player],' + @Output + '
INTO #dbTemp2
FROM
(
SELECT [Player], [Start], [Average]
FROM #DbTemp1
) AS SourceTable
PIVOT
(
AVG([Average])
FOR [Start] IN (' + @Column + ')
) AS PivotTable
ORDER BY Len([Player]), Player;
select * from #DbTemp2'
EXECUTE(@SQL)