如何在SQL Server 2005中将行转换为列 [英] How do i transform rows into columns in sql server 2005
问题描述
此处有一个问题在具有相同标题的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做到这一点?
也有类似的问题此处以stackoverflow的方式回答.
您需要使用运算符 PIVOT 来实现此目的.以下是示例和有关如何执行此操作的说明.该示例可从 2. 即 3.此查询提取创建交叉表结果所需的所有数据行.查询后的(p)将创建一个临时结果表,然后该表可用于满足步骤1的查询. 4.PIVOT表达式 进行实际的汇总,并将结果放入称为pvt的临时表中,作为 There is a question here in stackoverflow with the same title but that is not what I am looking for. I need to trasform this result set into something like this 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. Explanation 1.The first part of the query gives you a nice flattened result of your Name column values in a single row as follow You can learn more about the STUFF and XML PATH here and here. 2. i.e 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. 4.The PIVOT expression does the actual summarization and puts the results into a temporary table called pvt as
这篇关于如何在SQL Server 2005中将行转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋![Cheryl],[Drew],[Karen],[Kath],[Kirk],[Matt]
SELECT + @cols + FROM
将选择所有行作为最终结果集的列名(pvt-步骤3)Select [Chery],[Drew],[Morgan],[Kath],[Kirk],[Matt]
(SELECT t1.Name, t1.Count FROM TESTTABLE AS t1) p
PIVOT (MAX (Count) FOR Name IN ( @cols) AS pvt
Chery | Drew | Morgon | Kath | Kirk | Matt
-------------------------------------------
257 1500 13 500 200 76
I have a table like the one below 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
---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)
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]
SELECT + @cols + FROM
will select all the rows as coloumn names for the final result set (pvt - step 3) Select [Chery],[Drew],[Morgan],[Kath],[Kirk],[Matt]
(SELECT t1.Name, t1.Count FROM TESTTABLE AS t1) p
PIVOT (MAX (Count) FOR Name IN ( @cols) AS pvt
Chery | Drew | Morgon | Kath | Kirk | Matt
-------------------------------------------
257 1500 13 500 200 76