Results 1 to 7 of 7

Thread: cross-tab query

  1. #1
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47

    Unanswered: cross-tab query

    Hi all.

    we have a department that has an access database with a bunch of queries in it. They want us to convert it to sql server. One of the queries is a cross-tab query. Is there an easy way to create this in sql? the column headings are the value of column from a table. This could change each month that they run it. How do I make the column heading a variable? I'm guessing a stored procedure would be best. Does anyone have any suggestions?

    Thanks so much.
    ODaniels

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What version of SQL Server?


    If 2000, then no, there is no "easy" way. This is one of the more irritating omissions from SQL 2k.

    If you have 2k5, then you're good to go.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it's not too hard in sql 2k. there is even a section in BOL if you need a how to.
    “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.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The section in BOL requires you to know all of the "column" values in advance...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    It's sql2k.
    That's what I saw also - need to know all the column values in advance.
    I can build the table with a bunch of statements, I was just hopeing there was an easier way.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I have a crosstab sproc tucked away somewhere. I'll dig it up in a bit here.


    ... and there she is:
    Code:
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS OFF 
    GO
    
    
    
    
    
    
    
    
    
    CREATE PROCEDURE crosstab @XField varChar(50), @XTable varChar(50),
    @XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(20), @XRow varchar(40)
     AS
    Declare @SqlStr nvarchar(4000)
    Declare @tempsql nvarchar(4000)
    Declare @SqlStrCur nvarchar(4000)
    Declare @col nvarchar(100)
    
    set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + ']  ' + @XWhereString + ' Group By [' + @XField + ']'
    
    exec sp_executesql @sqlstrcur
    
     declare xcursor Cursor  for  Select * from ##temptbl_Cursor 
     open xcursor 
    
     Fetch next from  xcursor 
     into @Col 
     
    While @@Fetch_Status = 0
    Begin
      set @Sqlstr = @Sqlstr + ", "
      set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col +
                               "' then [" + @XFunctionField + "] Else 0 End) As [" + @Col + "]" ,'')
      set @Sqlstr = @tempsql
      Fetch next from xcursor into @Col
     
    End
    
     set @tempsql = 'Select '  + @XRow + ', ' + @Sqlstr + ' From ' + @XTable +
                                 @XWhereString +   ' Group by ' + @XRow + ' Order By ' + @XRow 
     set @Sqlstr = @tempsql
    PRINT @tempsql
     Close xcursor
     Deallocate xcursor  
    
     set @tempsql = N'Drop Table ##temptbl_Cursor'
     exec sp_executesql @tempsql
    
     exec sp_executesql @Sqlstr
    
    
    
    
    
    
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    I should add that I shameless stole this from some random website, though I can't remember where it was.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2005
    Location
    Jersey
    Posts
    47
    You are awsome Teddy.
    I will give that a try. Thank you.

Posting Permissions

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