Removing zero or null columns from a query.

[svankley]

I have a SQL2000 database that contains batch data. In this example it's a cookie dough plant.
I want to do a query to group the 3 types of batch runs below based on the prod_ID field but I don't want any blank or NULL columns returned.
Is there a way to do a query I can put into a report that will return the parent/child recordsets grouped by the Prod_ID with the zero or Null columns for each of the 3 groups. I think this sounds confusing but I hope you guys can help me out with this one! 8)
Sample data is below:

Batch_Number Prod_ID Prod_ID_Desc Bin1_W Bin2_W Bin3_W Bin4_W Bin5_W Bin6_W
------------ ----------- ---------------------- ------- ----------- ----------- ----------- ----------- -----------
0 6 Red Cookie 222 123 98 0 0 NULL
1 6 Red Cookie 234 144 76 0 0 NULL
2 6 Red Cookie 232 153 88 0 0 NULL
3 7 Blue Cookie 44 0 123 222 0 NULL
4 7 Blue Cookie 44 0 123 222 0 NULL
5 7 Blue Cookie 44 0 123 222 0 NULL
6 7 Blue Cookie 44 0 123 222 0 NULL
1 8 Yellow Cookie 44 812 395 0 118 NULL
1 8 Yellow Cookie 44 792 414 0 119 NULL

The results I'ld like to achieve would look like this:

Prod_ID_Desc Bin1_W Bin2_W Bin3_W
---------------------- ------- ------- -------
Red Cookie 222 123 98
Red Cookie 234 144 76
Red Cookie 232 153 88
TOTAL 688 420 262

Prod_ID_Desc Bin1_W Bin3_W Bin4_W
---------------------- ------- ------- -------
Blue Cookie 44 123 282
Blue Cookie 44 123 233
Blue Cookie 44 123 242
Blue Cookie 44 123 229
TOTAL 132 492 986

Prod_ID_Desc Bin1_W Bin2_W Bin3_W Bin5_W
---------------------- ------- ------- ------- -------
Yellow Cookie 44 812 395 118
Yellow Cookie 44 792 414 119
TOTAL 88 1604 809 237

 

[svankley]

Sorry, the 3rd sentence should read:
Is there a way to do a query I can put into a report that will return the parent/child recordsets grouped by the Prod_ID WITHOUT the zero or Null columns for each of the 3 groups.

 

[Crowley16]

you could try to build a dynamic query(s)

have a select test each column to see if it includes 0 or nulls, and based on the result, generate a string parameter, the use EXEC to run that string as a query...

--------------------
Procrastinate Now!