Given I have the following view where N=1..100
detail_view_N Pant Quantity Fieldx ... A 20 A 13 B 4
Currently we have summary views which insert to a table like
summary_view_N Report_Name Plant Count summary_view_1 A 2 summary_view_1 B 1
The table is then used to create an overall summary like
summary_view_all_plants Report_Name Plant Count summary_view_1 A 2 summary_view_1 B 1 ... summary_view_N X Y
Is there a way to create summary_view_all_plants without having to create each individual summary_view_N? I would like to be able to iterate though a list of reports and dynamically generate the insert views.
Advertisement
Answer
Found a good solution to this. I have a table which holds the names of the views which I want to summarize (detail_view_Names). I go thought each Report Name and build a query that performs the summary of each report.
DECLARE @REPORT_ID nvarchar(50),
@sqlCommand varchar(1000)
DECLARE REPORT_cursor CURSOR
FOR SELECT Report_Name
FROM detail_view_Names
OPEN REPORT_cursor
FETCH NEXT FROM REPORT_cursor INTO @REPORT_ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'SELECT ''' + @Report_ID + ''' AS ReportName, Plant, COUNT(*) AS [Count] FROM dbo.' + @Report_ID + ' GROUP BY Plant'
EXEC (@sqlCommand)
FETCH NEXT FROM REPORT_cursor INTO @REPORT_ID
END
CLOSE REPORT_cursor
DEALLOCATE REPORT_cursor
To add a new report to the summary, just add a new report to the detail_view_Names