pivot - Transpose a set of rows as columns in SQL Server 2000

Question

Is there any facility of transposing rows to columns in SQL Server (it is possible in MS-Access)? I was befuddled because this facility is available in MS-Access but not in SQL Server. Is it by design that this feature has not been included in SQL Server?

Answer 1

The example at http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm only works if you know in advance what the row values can be. For example, let's say you have an entity with custom attributes and the custom attributes are implemented as rows in a child table, where the child table is basically variable/value pairs, and those variable/value pairs are configurable.

color red
size  big
city  Chicago

I'm going to describe a technique that works. I've used it. I'm NOT promoting it, but it works.

To pivot the data where you don't know what the values can be in advance, create a temp table on the fly with no columns. Then use a cursor to loop through your rows, issuing a dynamically built "alter table" for each variable, so that in the end your temp table has the columns, color, size, city.

Then you insert one row in your temp table, update it via another cursor through the variable, value pairs, and then select it, usually joined with its parent entity, in effect making it seem like those custom variable/value pairs were like built-in columns in the original parent entity.