January 26, 2009 at 8:19 pm
Hi Lynn -
Still trying to work it out. Thanks for checking in.
January 26, 2009 at 9:13 pm
We can help you out if you provide a sample data set and the expected results based on the sample data set. Your expected results you did post seems to indicate that you actually need 3 separate queries, and there is also some data mapping needed to ensure the results are properly tabulated.
January 27, 2009 at 6:41 am
Yes, I am using more then one query against the function I wrote. Now I am trying to figure out how I can do a cross tab to include the counties. Anyway, thanks.
January 27, 2009 at 6:52 am
The last two articles linked in my signature block may be of help. Give them a quick read and let me know if they do.
January 27, 2009 at 7:55 am
I came across the following crosstab procedure. It's fine with the exception that I can't have more then one done...I want to use this to get the counties...how would you be able to add more then one???
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[crosstab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
January 27, 2009 at 8:11 am
It doesn't seem to be an efficient way of doing a cross-tab.
When doing this:
exec crosstab 'Select Month FROM GetTotalOf1()
WHERE Month =1 GROUP BY Month','SUM(Month)','County','GetTotalOf1()'
It takes 4:31 to run. It would take way more if trying to do multiple with this. It gives me what I am looking for though. Counties across top and the Sum for each month.
Month 2 313 15 16 23 33
16639911103011187101230
Oh well!
Viewing 6 posts - 46 through 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply