问题描述
这里有一个问题在具有相同标题的 stackoverflow 中，但这不是我要找的.
我有一张像下面这样的桌子
名称 |数数----------------奇瑞 |257德鲁 |1500摩根 |13凯丝 |500柯克 |200马特 |76我需要将这个结果集转换成这样的东西
奇瑞 |德鲁 |摩根 |凯丝 |柯克 |马特-------------------------------------257 1500 13 500 200 76我如何使用 sql server 2005 实现这一点?
也有类似的问题 此处，此处 在 stackoverflow 中回答.
您需要使用运算符 PIVOT 在您的查询中实现此目的.这是有关如何执行此操作的示例和说明.该示例引用自 this 来源.
---我假设你的表名是TESTTABLE---声明@cols NVARCHAR(2000)声明 @query NVARCHAR(4000)SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 %'],[' + t.名称从可测试为 tORDER BY '],[' + t.NameFOR XML 路径('')), 1, 2, '') + ']'SET @query = N'SELECT '+ @cols +' FROM(SELECT t1.Name , t1.Count FROM TESTTABLE AS t1) pPIVOT (MAX([Count]) FOR Name IN ('+ @cols +'))AS pvt;'执行(@查询)说明
1.查询的第一部分
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT'],[' + t.名称从可测试为 tORDER BY '],[' + t.NameFOR XML 路径('')), 1, 2, '') + ']'在单行中为您的 Name 列值提供一个很好的扁平化结果，如下所示
[Cheryl],[Drew],[Karen],[Kath],[Kirk],[Matt]您可以在此处了解有关 STUFF 和 XML PATH 的更多信息 和 此处.
2.SELECT + @cols + FROM 将选择所有行作为最终结果集的列名(pvt - 步骤 3)
即
选择[奇瑞]、[德鲁]、[摩根]、[凯斯]、[柯克]、[马特]3.此查询提取我们创建交叉表结果所需的所有数据行.查询之后的 (p) 会创建一个结果的临时表，然后可以使用该表来满足步骤 1 的查询.
(SELECT t1.Name, t1.Count FROM TESTTABLE AS t1) p4.PIVOT 表达式
PIVOT (MAX (Count) FOR Name IN (@cols) AS pvt进行实际的总结并将结果放入一个名为 pvt 的临时表中作为
奇瑞 |德鲁 |摩根 |凯丝 |柯克 |马特-------------------------------------257 1500 13 500 200 76There is a question here in stackoverflow with the same title but that is not what I am looking for.
I have a table like the one below
Name | Count
----------------
Chery | 257
Drew | 1500
Morgon | 13
Kath | 500
Kirk | 200
Matt | 76
I need to trasform this result set into something like this
Chery | Drew | Morgon | Kath | Kirk | Matt
-------------------------------------------
257 1500 13 500 200 76
How do i acheive this using sql server 2005?
There are similar questions here,here answered in stackoverflow.
You need to use the operator PIVOT in your query to acheive this.Here is the example and explanation on how you can do that.The example is referenced from this source.
---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)
Explanation
1.The first part of the query
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t.Name
FROM TESTTABLE AS t
ORDER BY '],[' + t.Name
FOR XML PATH('')
), 1, 2, '') + ']'
gives you a nice flattened result of your Name column values in a single row as follow
[Cheryl],[Drew],[Karen],[Kath],[Kirk],[Matt]
You can learn more about the STUFF and XML PATH here and here.
2.SELECT + @cols + FROM will select all the rows as coloumn names for the final result set (pvt - step 3)
i.e
Select [Chery],[Drew],[Morgan],[Kath],[Kirk],[Matt]
3.This query pulls all the rows of data that we need to create the cross-tab results. The (p) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.
(SELECT t1.Name, t1.Count FROM TESTTABLE AS t1) p
4.The PIVOT expression
PIVOT (MAX (Count) FOR Name IN ( @cols) AS pvt
does the actual summarization and puts the results into a temporary table called pvt as
Chery | Drew | Morgon | Kath | Kirk | Matt
-------------------------------------------
257 1500 13 500 200 76
这篇关于我如何在 sql server 2005 中将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！