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.
ODanielsWhat 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.|||it's not too hard in sql 2k. there is even a section in BOL if you need a how to.|||The section in BOL requires you to know all of the "column" values in advance...|||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.|||I have a crosstab sproc tucked away somewhere. I'll dig it up in a bit here.

... and there she is:

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.|||You are awsome Teddy.
I will give that a try. Thank you.