在 SQL Server 2000 中将一组行转换为列 [英] Transpose a set of rows as columns in SQL Server 2000

问题描述

在 SQL Server 中是否有任何将行转换为列的功能(在 MS-Access 中可以)?我很困惑，因为这个工具在 MS-Access 中可用，但在 SQL Server 中不可用.SQL Server 中没有包含此功能是设计使然吗?

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?

推荐答案

示例在 http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm 仅在您事先知道行值可以是什么时才有效.例如，假设您有一个具有自定义属性的实体，并且自定义属性被实现为子表中的行，其中子表基本上是变量/值对，并且这些变量/值对是可配置的.

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.

这篇关于在 SQL Server 2000 中将一组行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！