CODEWITHSUNDEEP
sqlt-sqlsql-server-2005stored-proceduresgroup-by
HOY
HOY

Reputation: 1007

Group By Column Name As parameter

Let's say that I have a table called Sales.

I also have a stored procedures something like this:

SELECT A FROM SALES GROUP BY A

SELECT B FROM SALES GROUP BY B

SELECT C FROM SALES GROUP BY C

SELECT D FROM SALES GROUP BY D

SELECT E FROM SALES GROUP BY E

Can I reduce the number of SELECT statements in that stored procedure to just one?

In other words is it possible to use the column name in group by as a parameter?

Upvotes: 0

Views: 2279

Answers (2)

gbn
gbn

Reputation: 432210

Your idea breaks the stored procedure contract unless all 5 columns are compatible datatypes because the return type changes each time. This means more complex client code to deal with this return type change

If they are all the same datatype, to avoid dynamic SQL you can use this

SELECT DISTINCT
   CASE @param
     WHEN @param = 'A' THEN A
     WHEN @param = 'B' THEN B
     WHEN @param = 'C' THEN C
     WHEN @param = 'D' THEN D
     WHEN @param = 'E' THEN E
   END
FROM
   SALES;

It still isn't efficient though.

If you want different aggregates like SUM or COUNT there are other ways to do this without datatype compatibility or dynamic SQL. So, what is the actual problem you want to solve anyway?

For example, this means you can pick A to E in the client cleanly

SELECT DISTINCT
    COUNT(*) OVER (GROUP BY A) AS cntA,
    COUNT(*) OVER (GROUP BY B) AS cntB,
    COUNT(*) OVER (GROUP BY C) AS cntC,
    COUNT(*) OVER (GROUP BY D) AS cntD,
    COUNT(*) OVER (GROUP BY E) AS cntE
FROM
    SALES;

Upvotes: 1

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Option A - you can use dynamic sql:

DECLARE @sql NVARCHAR(MAX)
SELECT 'SELECT ' + @col + ' FROM SALES GROUP BY ' + col + '; '
EXEC (@sql)

Option B - you can use CASE

SELECT CASE @col WHEN 'A' THEN A
                 WHEN 'B' THEN B
                 WHEN 'C' THEN C
                 WHEN 'D' THEN D
                 WHEN 'E' THEN E
         END
FROM SALES
GROUP BY CASE @col WHEN 'A' THEN A
                   WHEN 'B' THEN B
                   WHEN 'C' THEN C
                   WHEN 'D' THEN D
                   WHEN 'E' THEN E
         END

(Both options receive @col as string parameter)

Upvotes: 3

Related Questions