Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    13

    Unanswered: Problems formatting my table...

    I have a table that looks like this...

    Code:
    City    State    Server    Type
    Chicago  IL        Svr1      Data
    Chicago  IL        Svr2      Data
    Chicago  IL        Svr3      Backup
    Chicago  IL        Svr4      Backup
    Atlanta  GA        Svr1      Data
    Atlanta  GA        Svr2      Data

    I already have a function to convert the server rows into a comma delimited string...
    Code:
    ALTER        function dbo.fnGetServers (@City varchar(25), @State varchar(25), @Type varchar(25), @Tree varchar(25))
    returns varchar(1000)
    as
    begin
    declare @NewSvrCol varchar(1000)
    select @NewSvrCol = ''
    select @NewSvrCol = @NewSvrCol + Server + ', ' from serverops.dbo.v_userviews where city = @City and State = @State and Type = @Type and Tree = @Tree 
    select @NewSvrCol = left(@NewSvrCol, len(@NewSvrCol)-1)
    return(@NewSvrCol)
    end
    Any suggestions on how to display the table in the following format?

    Code:
    City    State  DataSvrs    BackupSvrs
    Chicago  IL   Svr1,Svr2    Svr3,Svr4  
    Atlanta  GA   Svr1,Svr2    NULL

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    read the article titled Cross-Tab Reports in Books online and the read about CASE statements.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2006
    Posts
    13
    Quote Originally Posted by Thrasymachus
    read the article titled Cross-Tab Reports in Books online and the read about CASE statements.
    Thanks a lot, I've made some progress but am still having a slight problem...
    I am using this query:
    Code:
    ALTER       VIEW v_USAtlasAuthServers
    as
    select distinct u.City, u.State, 
    CASE  u.Type WHEN 'Data' then dbo.fnGetServers(City, State, Type) ELSE null END as Data_Servers,
    CASE  u.Type WHEN 'Backup' then dbo.fnGetServers(City, State, Type) ELSE null END as Backup_Servers 
    from serverops.dbo.v_userviews u
    
    where u.tree='tree1' and u.country='united states' and u.type in ('Data Server','Backup Server')
    group by u.city, u.state,  u.type

    which gives me the following table:

    Code:
    City    State  Data_Servers    Backup_Servers
    Chicago  IL        Svr1,Svr2         NULL 
    Chicago  IL        NULL            Svr3,Svr4 
    Atlanta  GA        Svr1,Svr2         NULL
    Which is not right as I want Chicago, IL to be in one row...any idea on how to fix this?

Posting Permissions

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