CODEWITHSUNDEEP
sqlsql-servert-sql
decompiled
decompiled

Reputation: 1921

Dynamic Cross Apply Values

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!

Upvotes: 6

Views: 19596

Answers (1)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

I have been posting to this site for a couple months now. It is not often that I pick up some syntax that I have not seen before.

Real cool stuff!

I usually put the code in a test environment. In this case, I used Adventure Works 2012.

enter image description here

The tool tip or picture tells a thousand words.

You have tuples in list of values. However, the TotalDue is not a string, it refers to the outer table [Sales].[SalesOrderHeader].

Replacing the inner cross apply with a SELECT will only bring back values to apply to every row. The reference to the outer table is lost.

Given this fact, you will need to create the SQL dynamically. You could create and fill a table with Column Names as sysname. Then write the code dynamically and execute it with sp_executesql.

Make sure this is an internal, reporting, query. You do not want to introduce SQL Injection.

Also, while the begin and works, it is really not that fastest or best approach. Date literals are the way to go for speed and accuracy.

Upvotes: 7

Related Questions