TSQL how to specify range of list columns for PIVOT?
Following is a SELECT statement from a stored procedure . I use to fetch data which is then sent to JSON:
SELECT *
FROM (
SELECT CAST(DateTimeUTC as SmallDateTime) as [DateTime], DataValue, VariableID
FROM DataValues
WHERE SiteID = @siteID and VariableID BETWEEN 9 and 10 and DateTimeUTC >= DATEADD (day, @pastDays, getdate())
) TableDate
PIVOT (SUM(DataValue) FOR VariableID IN ([9],[10])) PivotTable ORDER BY [DateTime]
What I would like to do is change the procedure to accept a range of columns to rotate. In the above example, I am only getting the values for two variables. What if I want to get VariableIDs from 1 to 10 or from 1 to 50? There must be a way to get 1 to 10 in a way other than:
VariableID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
The use of PIVOT and UNPIVOT on MSDN does not mention a way to specify a range.
I understand that it is possible to use dynamic SQL in a stored procedure, but given my own knowledge of SQL and the knowledge of those who will have to maintain this long term, I hesitate to introduce additional complexity with dynamic SQL. There is a conceptually similar question here, see TSQL Pivot Long List of Columns , which was answered with a dynamic SQL solution.
source to share
Unfortunately the function PIVOT
has no way to generate a list of columns without using dynamic sql.
So your SQL code will be like this:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(VariableID)
from DataValues
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT [DateTime], ' + @cols + ' from
(
SELECT CAST(DateTimeUTC as SmallDateTime) as [DateTime],
DataValue,
VariableID
FROM DataValues
WHERE SiteID = '+cast(@siteID as varchar(10))+'
-- and VariableID BETWEEN 9 and 10 -- remove the variableID filter
and DateTimeUTC >= DATEADD (day, +'cast(@pastDays as varchar(10))+', getdate())
) x
pivot
(
SUM(DataValue)
for VariableID in (' + @cols + ')
) p '
execute(@query)
The key to this is the following code, which generates the list variableIds
to become columns:
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(VariableID)
from DataValues
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
This will create a list of all distinct
id variables. This list is then appended to the SQL query string to return.
source to share