Dynamic Cross Apply Values

Viewed 14088

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!

1 Answers
Related