在SQL Server中将行透视成列 [英] Pivoting rows into columns in SQL Server

问题描述

我有一组看起来像这样的数据:

I have a set of data that looks like this:

FirstName   LastName   Field1   Field2   Field3 ... Field27
---------   --------   ------   ------   ------     -------
 Mark        Smith     A        B        C          D
 John        Baptist   X        T        Y          G
 Tom         Dumm      R        B        B          U

但是，我希望数据看起来像这样:

However, I'd like the data to look like this:

FirstName   LastName   Field   Value
---------   --------   -----   -----
Mark        Smith      1       A
Mark        Smith      2       B
Mark        Smith      3       C
Mark        Smith      4       D
John        Baptist    1       X
John        Baptist    2       T
John        Baptist    3       Y
John        Baptist    4       G
Tom         Dumm       1       R
Tom         Dumm       2       B
Tom         Dumm       3       B
Tom         Dumm       4       U

我已经看过PIVOT功能.它可能会起作用.我不太确定.我无法理解如何使用它.但是，我不确定枢轴是否可以在字段"列中放置"4".根据我的理解，PIVOT函数将简单地将Field1 ... Field27的值转换为值"列.

I have looked at the PIVOT function. It may work. I am not too sure. I couldn't make sense of how to use it. But, I am not sure that the pivot could place a '4' in the 'Field' column. From my understanding, the PIVOT function would simply transpose the values of Field1...Field27 into the 'Value' column.

我还考虑过使用游标遍历表，然后遍历字段列，然后将字段"和值"插入另一个表.但是，我知道这会影响性能，因为它是基于序列的操作.

I have also considered iterating over the table with a Cursor and then looping over the field columns, and then INSERTing into another table the 'Field's and 'Value's. However, I know this will impact performance since it's a serial-based operation.

任何帮助将不胜感激！如您所知，我对T-SQL(或通常的SQL)和SQL Server还是很陌生.

Any help would be greatly appreciated! As you can tell, I'm quite new to T-SQL (or SQL in general) and SQL Server.

推荐答案

您可以使用

You can perform with an UNPIVOT. There are two ways to do this:

1)在静态取消透视"中，您将对查询中的字段"列进行硬编码.

1) In a Static Unpivot you would hard-code your Field columns in your query.

select firstname
  , lastname
  , replace(field, 'field', '')  as field
  , value
from test
unpivot
( 
  value
  for field in (field1, field2, field3, field27)
) u

有关工作示例，请参见 SQL提琴.

See a SQL Fiddle for a working demo.

2)或者，您可以使用动态取消透视(Dynamic Unpivot)，当您运行SQL时，它将将项目列表获取到PIVOT.如果您要取消设置的字段很多，则动态"功能非常好.

2) Or you could use a Dynamic Unpivot which will get the list of items to PIVOT when you run the SQL. The Dynamic is great if you have a large amount of fields that you will be unpivoting.

create table mytest
(
  firstname varchar(5),
  lastname varchar(10),
  field1 varchar(1),
  field2 varchar(1),
  field3 varchar(1),
  field27 varchar(1)
)

insert into mytest values('Mark', 'Smith', 'A', 'B', 'C', 'D')
insert into mytest values('John', 'Baptist', 'X', 'T', 'Y', 'G')
insert into mytest values('Tom', 'Dumm', 'R', 'B', 'B', 'U')

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('mytest') and
               C.name like 'Field%'
         for xml path('')), 1, 1, '')

set @query = 'SELECT firstname, lastname, replace(field, ''field'', '''')  as field, value
            from mytest
            unpivot 
            (
               value
               for field in (' + @cols + ')
            ) p '

execute(@query)

drop table mytest

两者都会产生相同的结果.

Both will produce the same results.

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