如何在SQL Server 2005中将行转换为列

Question

在stackoverflow上有一个相同标题的问题，但那不是我要找的。
我有一个像下面这样的表：

Name   | Count  
----------------    
Chery  | 257  
Drew   | 1500
Morgon | 13  
Kath   | 500  
Kirk   | 200  
Matt   | 76 

我需要将这个结果集转换成类似于以下内容的东西。

Chery | Drew | Morgon | Kath | Kirk | Matt  
-------------------------------------------  
257     1500     13      500    200    76

如何使用SQL Server 2005实现这一目标？

- Sam

Answer 1

这里有类似的问题在这里,在这里在stackoverflow上得到了解答。

您需要在查询中使用运算符PIVOT来实现这一点。以下是示例和说明，说明如何做到这一点。该示例引用自此处资源。

---I assumed your tablename as TESTTABLE---
DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)

SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t.Name
                        FROM    TESTTABLE AS t
                        ORDER BY '],[' + t.Name
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

SET @query = N'SELECT '+ @cols +' FROM
(SELECT t1.Name , t1.Count FROM TESTTABLE AS t1) p
PIVOT (MAX([Count]) FOR Name IN ( '+ @cols +' ))
AS pvt;'

EXECUTE(@query)

解释

1.查询的第一部分

SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                        '],[' + t.Name
                FROM TESTTABLE AS t
                ORDER BY '],[' + t.Name
                FOR XML PATH('')
              ), 1, 2, '') + ']'

以下是将您的“名称”列值压缩为单行的漂亮结果：

[Cheryl],[Drew],[Karen],[Kath],[Kirk],[Matt]  

你可以在这里和这里了解有关STUFF和XML PATH的更多信息。

2. SELECT + @cols + FROM将选择所有行作为最终结果集(pvt - step 3)的列名。

i.e

Select [Chery],[Drew],[Morgan],[Kath],[Kirk],[Matt] 

3. 这个查询检索出我们需要创建交叉表结果的所有数据行。查询之后的(p)创建了一个临时表格，可以用来满足第一步查询的需求。

(SELECT t1.Name, t1.Count FROM  TESTTABLE AS t1) p

4. PIVOT表达式

PIVOT (MAX (Count) FOR Name IN ( @cols) AS pvt

执行实际的汇总并将结果放入一个名为pvt的临时表中。

Chery | Drew | Morgon | Kath | Kirk | Matt  
-------------------------------------------  
257     1500     13      500    200    76

- I Am Back

---

+1，非常好的解决方案和答案。请注意，TOP 100 PERCENT可以更改为TOP 30或其他数字，以避免在有大量行的情况下超出列数限制。 - KM.

Answer 2

参见使用 PIVOT 和 UNPIVOT。这里有两个关系运算符PIVOT和UNPIVOT，它们可以将一个表值表达式转换为另一个表格。 PIVOT旋转表值表达式，将表达式中一个列中的唯一值变成输出中的多个列，并在需要时对任何剩余的要在最终输出中使用的列值执行聚合。 UNPIVOT 执行与 PIVOT 相反的操作，将表值表达式的列旋转为列值。

SELECT Chery, Drew, Morgon, Kath, Kirk, Matt
FROM 
(SELECT [Name], [Count] From Foo)
PIVOT
(
   MIN([Count])
   FOR [Name] IN (Chery, Drew, Morgon, Kath, Kirk, Matt)
) AS PivotTable

- Eugene Yokota

Answer 3

如果你想避免使用像是数据透视表或是被确认的回答一样复杂的东西，你可以这样做！（大部分的代码都是为了设定测试数据，以防有人想要尝试）

/* set up your test table */
declare @TestData table (Name Varchar(80),[Count] int)    
insert into @TestData (Name, [count])
Select 'Chery' as name, 257 as [count]  
union all select 'Drew', 1500
union all select 'Morgon',13  
union all select 'Kath', 500  
union all select 'Kirk', 200  
union all select 'Matt', 76 

/* the query */
Declare @Query Varchar(max)
Select @Query=Coalesce(@query+', ','SELECT ') +Convert(VarChar(5),[count]) +' as ['+name+']'
from @TestData
Execute (@Query)
/* result 

Chery       Drew        Morgon      Kath        Kirk        Matt
----------- ----------- ----------- ----------- ----------- -----------
257         1500        13          500         200         76

*/

- Phil Factor