MS SQL Server - How to dynamically choose whether to SUM() column or GROU BY this column?

Author

How to dynamically choose whether to SUM() column or GROU BY this column?

2006-12-11, 7:13 pm

Hi there

I'm developping an application which would deal with costs in
organization which structure is split hierarchically to four division
levels.

eg. one can have atomic unit by setting values:
division1=A3
division2=111
division3=245
division4=1234

despite their hierarchical relation, my client wants to have reports on
all of those division combinations. so the perfect solution would be
the ultraGeneric(div1,di
v2,div3,div4) procedure which could do:

when called with all 4 parameters

select div1,div2,div3,div4,
SUM(cost)

where div1=@div1 and div2=@div2 and div3=@div3 and div4=@div4
group by div1,div2,div3,div4

while when called with only div1 set (rest would be NULL):

select div1,'all','all,'all
',SUM(cost)
where div1=@div1
group by div1

I know how I can deal with WHERE part:
WHERE (@DIV1 IS NULL OR @DIV1= '' OR DIV1=@DIV1)

but I have no idea what to do with SUM / GROUP BY part.

I can use CASE in select to choose whether to put column name or some
fake string if the column parameter is null.

but what about GROUP BY? does it behave like ORDER BY, in which I'd
have to use CASE with all combinations of parameter states?

thanks a lot
HP

2006-12-12, 12:12 am

sorry, the post title doesn't make sense. to restate the question:

How to choose whether or not GROUP by a given column depending on
whether its value in parameter is null or not?

Razvan Socol

2006-12-12, 5:17 am

You should simply GROUP BY all columns, and if there is a single value
in one of those columns (due to it being used in a condition), so be
it.

You can also use dynamic SQL. Read the following article by Erland
Sommarskog, for more approaches to the dynamic search conditions
problem:
http://www.sommarskog.se/dyn-search.html

Razvan

Sponsored Links