How can I do a dynamic pivot in SQL Server 2000

I want to write a SQL statement that use开发者_运维问答s a pivot in SQL Server 2000. The PIVOT keyword is not available in SQL Server 2000 so I found some examples that use a case statement but that requires that you know the column names beforehand which I won't. How do I do a pivot which dynamically generates the column names from the data it has available to it?

We create SQL commands with the CASE statements from our application and fire them at the database (any database, not specifically SQL server). First we determine the number of pivot columns and their names using one query, from those results we generate the next query. So the first query to determine the columns looks somewhat like:

SELECT DISTINCT myField FROM myTable

Then we use all the values in this result to construct an SQL command where a CASE statement is generated for each value.

We wanted a databasebase agnostic solution so we do this processing outside the database but i'm sure you could do the same in a stored procedure withing SQL server itself.

I have not tried to replicate PIVOT on SQL Server 2000 but what I have done is use PIVOT when I do not know the column names beforehand. I had used ROW_NUMBER() to determine the column names instead. You can try that.