Grouping on 7 Parameter Fields

[Crystalboy1]

hello All!

i have written a stored Procedure, i got 7 breakdown Options. for Example

A, B, C, D, E, F, G,

Now let say i got a table having calulted data in fields

tablename
(A,
b,
c,
d,
e,
f,
g,
CalulatedAccounts,
CalcultedSum,
TotalProfit)

now let say i am returing my data
************************************************
Example No 1
Now if i have selected A,D,G and the reset of paramenters that is B,C,E,F are NULL then i would return

Select
A,
D,
G,
SUM (CalulatedAccounts),
Sum (CalcultedSum),
Sum(TotalProfit)
from TableName
Group by
A,
D,
G
***********************************************


************************************************
Example No 2
Now if i have selected D,E,F and the reset of paramenters that is A,B,C,G are NULL then i would return

Select
D,
E,
F,
SUM (CalulatedAccounts),
Sum (CalcultedSum),
Sum(TotalProfit)
from TableName
Group by
D,
E,
F
***********************************************

************************************************
Example No 3
Now if i have selected A and the reset of paramenters that is B,C,D,E,F,G are NULL then i would return

Select
A,
SUM (CalulatedAccounts),
Sum (CalcultedSum),
Sum(TotalProfit)
from TableName
Group by
A
***********************************************

Inshort whatever parameters i select, i want to group by them.. and ignore the rest....
if i do that by
i need logic for dynamic SQL becuase if i do the normal repetative statments and do check for every columns i think its more then 60 combinations which will kill me...

 

[bborissov]

One way:

SELECT CASE WHEN @ParameterForA IS NULL
                 THEN NULL
            ELSE FieldA  END AS FieldA,
       CASE WHEN @ParameterForB IS NULL
                 THEN NULL
            ELSE FieldB  END AS FieldB,
......
       CASE WHEN @ParameterForG IS NULL
                 THEN NULL
            ELSE FieldG  END AS FieldG,
      SUM(CalulatedAccounts),
      SUM(CalcultedSum),
      SUM(TotalProfit)
from TableName
Group by CASE WHEN @ParameterForA IS NULL
                 THEN NULL
            ELSE FieldA  END,
       CASE WHEN @ParameterForB IS NULL
                 THEN NULL
            ELSE FieldB  END,
......
       CASE WHEN @ParameterForG IS NULL
                 THEN NULL
            ELSE FieldG  END

That way you ALWAYS will get the same resultset in your BackEnd.
The other way is to build dynamic string:

-- varchar(8000) for SQL Server 2000
DECLARE @sql varchar(max) 
DECLARE @GroupBy varchar(max)

SET @sql     = 'SELECT '
SET @GroupBy = ''

IF @ParameterForA IS NOT NULL
   BEGIN
       SET @sql     = 'A,'
       SET @GroupBy = 'A,'
   END

IF @ParameterForB IS NOT NULL
   BEGIN
       SET @sql     = 'B,'
       SET @GroupBy = 'B,'
   END
     
....
IF @ParameterForG IS NOT NULL
   BEGIN
       SET @sql     = 'G,'
       SET @GroupBy = 'G,'
   END

SET @sql = @sql +   ' SUM(CalulatedAccounts),
                      SUM(CalcultedSum),
                      SUM(TotalProfit)
                   from TableName '
IF LEN(@GroupBy) > 0
   SET @sql = @sql + ' GROUP BY '+
              LEFT(@GroupBy,LEN(@GroupBy)-1)

EXEC(@sql)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[bborissov]

O!
I forgot to say:
NOT TESTED

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.