I have some code that looks like this:
SELECT colname, SUM(value) AS items_sum, COUNT(value) AS items_count
FROM MyTable
CROSS APPLY (
VALUES ('Item1',Item1),('Item2',Item2)
) x(colname, value)
WHERE Date BETWEEN @sdate AND @edate
AND value IS NOT NULL
GROUP BY colname
I have been asked to change the query to no longer hard code Item1, Item2, but to accept any Item#'s that are passed in. I have tried a few solutions to get the Item# via select statements but it seems the aggregate functions SUM() & COUNT() complain because the solutions I have tried return a varchar of the column name, but not the column data itself. Is there a way to pass the column Item# without using string concatenation?
Thanks!