Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    2

    Unanswered: Yet another Crosstab problem

    Hi all,
    i see there are many posts on crosstab queries in this forum but i can't seem to find a solution to my problem. hope you can help me.

    I have a view of server crashes:

    [dirty shutdown] [previous clean] [server name]
    2005-10-01 2005-09-01 srv1
    2005-11-01 2005-10-10 srv2

    which displays the date of a dirty shutdown of a server, and the date of the previous clean shutdown of this server plus the server name

    Also, i have a view of alerts per server:

    [alert id] [server name] [alert date] [alert name] [repeat count]
    123 srv1 2005-09-05 an alert 0
    124 srv1 2005-09-10 another alert 1
    125 srv1 2005-09-20 an alert 0
    126 srv2 2005-10-20 something else 0

    If [repeat count] is 0, the alert was given once, if it is 1, there were 2 alerts etc.

    Now, what i want (well not me but the guy i work for) is a view that displays all alerts between the clean and the dirty shutdown, per server:

    [dirty] [clean] server total "an alert" "another alert" "something else"

    2005-10-01 2005-09-01 srv1 4 2 2 0
    2005-11-01 2005-10-10 srv2 1 0 0 1

    Total is the total number of alerts for the server between the two dates. Of course, there are many servers, and the number of alert names varies over time which means i can't use "case when...".

    Is this possible??? Would be extremely thankful for any help!

    Regards,
    Elisabet

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    u have to create sp which dynamically generate dynamic sql query contain case statements and execute that query which will give desire result.Hope this will help to kick off urself.come back if have doubts


    Or try this Sp



    create PROC sp_CrossTab
    @table AS sysname, -- Table to crosstab
    @onrows AS nvarchar(500), -- Grouping key values (on rows)
    @onrowsalias AS sysname = NULL, -- Alias for grouping column
    @oncols AS nvarchar(1000), -- Destination columns (on columns)
    @sumcol AS sysname = NULL, -- Data cells
    @whereclause as nvarchar(500)= NULL -- where clause
    AS
    DECLARE
    @sql AS varchar(8000),
    @NEWLINE AS char(1)

    SET @NEWLINE = CHAR(10)

    -- step 1: beginning of SQL string
    SET @sql =
    'SELECT' + @NEWLINE +
    ' ' + @onrows +
    CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
    END


    CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

    DECLARE @keyssql AS varchar(1000)
    SET @keyssql =
    'INSERT INTO #keys ' +
    'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
    'FROM ' + @table
    --print @keyssql
    EXEC (@keyssql)


    DECLARE @key AS nvarchar(100)
    SELECT @key = MIN(keyvalue) FROM #keys

    WHILE @key IS NOT NULL
    BEGIN
    SET @sql = @sql + ',' + @NEWLINE +
    ' SUM(CASE CAST(' + @oncols +
    ' AS nvarchar(100))' + @NEWLINE +
    ' WHEN N''' + @key +
    ''' THEN ' + CASE
    WHEN @sumcol IS NULL THEN '1'
    ELSE @sumcol
    END + @NEWLINE +
    ' ELSE 0' + @NEWLINE +
    ' END) AS [' + @key+ ']'

    SELECT @key = MIN(keyvalue) FROM #keys
    WHERE keyvalue > @key
    END

    SET @sql = @sql + @NEWLINE +
    'FROM ' + @table + @NEWLINE
    if @whereclause is not null
    SET @sql = @sql+ 'WHERE ' + @whereclause + ' '+ @NEWLINE
    SET @sql = @sql+ 'GROUP BY ' + @onrows + @NEWLINE
    SET @sql = @sql+ 'ORDER BY ' + @onrows

    --PRINT @sql + @NEWLINE -- For debug
    EXEC (@sql)



    --- from Sql magazine
    ----------------------------
    ----example-----------
    Use northwind


    EXEC sp_CrossTab
    @table = 'Orders',
    @onrows = 'MONTH(OrderDate)',
    @onrowsalias = 'OrderMonth',
    @oncols = 'YEAR(OrderDate)',
    @whereclause=OrderDate between ''2005-09-05'' and ''2005-09-10'''

    USE pubs

    EXEC sp_CrossTab
    @table = 'sales',
    @onrows = 'stor_id',
    @oncols = 'YEAR(ord_date)',
    @sumcol = 'qty'
    Last edited by mallier; 11-15-05 at 07:15.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •