在SQL Server中转置表格 [英] Transposing a table in SQL server

问题描述

我正在为我的应用程序使用SQL Server 2005. 我的存储过程中有一个表，该表有两列C1和C2.我想转置此表，以使列C1的值成为这些列. 转置前(表1):

I am using SQL Server 2005 for my application. I have a table in my stored procedure which has two columns, C1 and C2. I want to transpose this table such that the values of column C1 becomes the columns. Before transpose (Table 1):

C1  C2
M1  U1
M1  U2
M1  U3
M2  U4
M2  U5

转置后(表2):

M1  M2
U1  U4
U2  U5
U3  NULL

在表1中，不同值(M1，M2)的数量可能会有所不同.因此，表2中的列不是固定的.

In Table1, the number of distinct values (M1, M2) may vary. So, the columns in Table2 are not fix.

请提供一种解决方案.

推荐答案

对于这种类型的数据转换，您将要使用SQL Server 2005+中可用的PIVOT函数.有两种方法可以应用 pivot 函数.

For this type of data transformation you will want to use the PIVOT function that is available in SQL Server 2005+. There are two ways to apply the pivot function.

如果您提前知道这些值，则可以在查询中对这些值进行硬编码.与此类似:

If you know the values ahead of time, then you can hard-code the values in the query. Similar to this:

select M1, M2
from
(
  select c1, c2,
    row_number() over(partition by c1 order by c1, c2) rn
  from yourtable
) src
pivot
(
  max(c2)
  for c1 in (M1, M2)
) piv

请参见带演示的SQL提琴.

但是，如果要转置为列的值的数量未知，则可以在运行时使用动态SQL创建查询.

But if you have an unknown number of values that you want to transpose into columns, then you can use dynamic SQL to create the query at run-time.

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(C1) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
             (
                select C1, C2,
                  row_number() over(partition by c1 order by c1, c2) rn
                from yourtable
            ) x
            pivot 
            (
                max(C2)
                for C1 in (' + @cols + ')
            ) p '

execute(@query)

请参见带演示的SQL提琴.

两者都会给出相同的结果，不同之处在于，如果值会更改，则动态版本是灵活的:

Both will give the same result, the difference is the dynamic version is flexible if the values will change:

| M1 |     M2 |
---------------
| U1 |     U4 |
| U2 |     U5 |
| U3 | (null) |

这篇关于在SQL Server中转置表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！