在 SQL Server 中转置表

2022-01-22 00:00:00 pivot sql transpose sql-server

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

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)的数量可能会有所不同.因此，Table2 中的列不固定.

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

请提供一个解决方案来达到同样的效果.

Please provide a solution to achieve the same.

推荐答案

对于这种类型的数据转换，您需要使用 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 Fiddle with Demo.

但是，如果您有未知数量的值要转置到列中，则可以使用动态 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 Fiddle with Demo.

两者都会给出相同的结果，不同的是动态版本是灵活的，如果值会改变:

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) |

相关文章