将行移入SQL Server 2005中的列 [英] Transposing Rows in to colums in SQL Server 2005
本文介绍了将行移入SQL Server 2005中的列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个SQL查询从表名中选择*",其输出是
I have an sql query "Select * from tablename" whose output is
col1 col2
A 1
B 2
C 3
我要将上述输出修改为以下内容
I want to modify the above output to below as following
A B C
1 2 3
请让我知道如何实现这一目标
Please let me know how could I achieve this
推荐答案
You will need to perform a PIVOT
. There are two ways to do this with PIVOT, either a Static Pivot where you code the columns to transform or a Dynamic Pivot which determines the columns at execution.
静态枢轴:
SELECT *
FROM
(
SELECT col1, col2
FROM yourTable
) x
PIVOT
(
min(col2)
for col1 in ([A], [B], [C])
)p
请参见带有演示的SQL小提琴
动态枢轴:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(col1)
from t1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + ' from
(
select col1, col2
from t1
) x
pivot
(
min(col2)
for col1 in (' + @cols + ')
) p '
execute(@query)
请参见带演示的SQL小提琴
如果不想使用PIVOT
函数,则可以使用CASE
语句执行类似的查询:
If you do not want to use the PIVOT
function, then you can perform a similar type of query with CASE
statements:
select
SUM(CASE WHEN col1 = 'A' THEN col2 END) as A,
SUM(CASE WHEN col1 = 'B' THEN col2 END) as B,
SUM(CASE WHEN col1 = 'C' THEN col2 END) as C
FROM t1
请参见带有演示的SQL小提琴
这篇关于将行移入SQL Server 2005中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文