SQL Server - Help in writing a dynamic SQL query.

Hi!

I have a report that looks like this :

Category    Region    Name    Amount($)
ABC        Americas  Test1      100
ABC        Americas  Test2      200
ABC Total                        300  
PQR        Asia      Test1      500      
PQR        Asia      Test2      400
PQR Total                        900     
XYZ        Europe    Test1      300
XYZ        Europe    Test2      200
XYZ        Europe    Test3      100
XYZ Total                         600
Grand Total                       1800


Now, I am currently writing a query that gives the users the data in the above report format.
But the way I am achieveing this is using queries as below :

select Category, Region, Name, SUM(Amount) Amount($) from mytable
where mytable.[Category] = 'ABC'
union
select 'ABC Total', '', '', SUM(Amount) Amount($) from mytable
where mytable.[Category] = 'ABC'

and so on for all the different categories in mytable.

Is there any way so that I can get the data in the report format displayed above dynamically?
i.e. with any new Category added in the table above, e.g. DEF, the DEF Total gets added to the report
and the grand total also gets adjusted automatically.

Any help would be much appreciated.

Regards,
Charvi.

Hi Charvi,

To dynamically create such report you could use cursor to load your category table and iterate through it providing the category for your queries automatically.
Normally cursors are something considered to avoid performance-wise, but I assume here that your report is something done periodically and there is no big concern on the performance of your script. In addition DB design, amount of data and your queries would contribute to the performance of query/script so I usually start with easiest and quickest way to achieve given goal as it might work perfectly in given situation and there might be no need to overdo it.
Alternative could be writing query with WHILE loop, for example.

Check the SQL online books or google up on SQL CURSOR for more information as the syntax and use is pretty simple. If you will still have problems writing your script let me know, and I will help you further.

Regards,
Robert