在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屋！