Stumbling on a SQL query - Help!!

  • Hi Lynn -

    Still trying to work it out. Thanks for checking in.

  • 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.

  • 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.

  • 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.

  • 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)

  • 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