server多列转行 sql_SQL Server 行转列，列转行。多行转成一列

一、多行转成一列(并以","隔开)

表名：A

表数据：

想要的查询结果：

查询语句：

SELECTname ,

value= ( STUFF(( SELECT ',' +valueFROMAWHERE name =Test.nameFORXML PATH('')

),1, 1, '') )FROM A ASTestGROUP BY name;

PS：STUFF语句就是为了去掉第一个【逗号】

附STUFF用法：(从原字符的第二个开始共三个字符替换为后面的字符)

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

查询结果：aijklmnef

二、一列转成多行

表名：tb

表数据：

想要的结果：

查询语句：

SELECT a.[name],b.[value]

FROM (SELECT [name],[value]=CAST(''+REPLACE([value],',','')+'' AS xml) FROMtb) aOUTER APPLY (SELECT [value]=T.C.value('.','varchar(50)') FROM a.[value].nodes('/v') AS T(C)) b

三、行转列(转自大神张志涛的博客 http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html)

1、建立表格

IF OBJECT_ID('tb') IS NOT NULL DROP TABLEtbgo

CREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)INSERT INTO tb VALUES('张三','语文',74)INSERT INTO tb VALUES('张三','数学',83)INSERT INTO tb VALUES('张三','物理',93)INSERT INTO tb VALUES('李四','语文',74)INSERT INTO tb VALUES('李四','数学',84)INSERT INTO tb VALUES('李四','物理',94)go

SELECT * FROM tb

2、使用SQL Server 2000静态SQL

SELECT姓名,max(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 end)语文,max(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 end)数学,max(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 end)物理FROMtbGROUP BY 姓名

3、使用SQL Server 2005静态SQL

SELECT *

FROM tb PIVOT( MAX(分数) FOR 课程 IN ( 语文, 数学, 物理 ) ) a;

4、使用SQL Server 2005动态SQL

--使用stuff()

DECLARE @sql VARCHAR(8000)SET @sql='' --初始化变量@sql

SELECT @sql=@sql+','+课程 FROM tb GROUP BY 课程 --变量多值赋值

SET @sql=stuff(@sql,1,1,'')--去掉首个','

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

exec(@sql)--或使用isnull()

DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+',','')+课程 FROM tb GROUP BY课程SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

exec(@sql)

四、行转列结果加上总分、平均分

1、使用SQL Server 2000静态SQL

SELECT姓名,max(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 end)语文,max(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 end)数学,max(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 end)物理,sum(分数)总分,cast(avg(分数*1.0)AS DECIMAL(18,2))平均分FROMtbGROUP BY 姓名

2、使用SQL Server 2000动态SQL

DECLARE @sql VARCHAR(500)SET @sql='select 姓名'

SELECT @sql=@sql+',max(case 课程 when'''+课程+'''then 分数 else 0 end)['+课程+']'

from(SELECT DISTINCT 课程 FROMtb)aSET @sql=@sql+',sum(分数) 总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名'

exec(@sql)

3、使用SQL Server 2005静态SQL

SELECT m.*,n.总分,n.平均分from(SELECT * FROM tb pivot(max(分数)FOR 课程 IN(语文,数学,物理))a)m,

(SELECT 姓名,sum(分数) 总分,cast(avg(分数*1.0)AS DECIMAL(18,2))平均分FROMtbGROUP BY姓名)nWHERE m.姓名=n.姓名

4、使用SQL Server 2005动态SQL

--使用stuff()

--

DECLARE @sql VARCHAR(8000)SET @sql='' --初始化变量@sql

SELECT @sql=@sql+','+课程 FROM tb GROUP BY 课程 --变量多值赋值

--同select @sql = @sql + ','+课程from (select distinct课程from tb)a

SET @sql=stuff(@sql,1,1,'')--去掉首个','

SET @sql='select m.* , n.总分,n.平均分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in ('+@sql+')) b) m ,

(select 姓名,sum(分数) 总分, cast(avg(分数*1.0) as decimal(18,2))平均分 from tb group by 姓名) n

where m.姓名= n.姓名'

exec(@sql)--或使用isnull()

DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+',','')+课程 FROM tb GROUP BY课程SET @sql='select m.* , n.总分, n.平均分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in ('+@sql+')) b) m ,

(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2))平均分 from tb group by 姓名) n

where m.姓名= n.姓名'

exec(@sql)

五、列转行

1、建立表格

IF OBJECT_ID('tb')IS NOT NULL DROP TABLEtbgo

CREATE TABLE tb(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)INSERT INTO tb VALUES('张三',74,83,93)INSERT INTO tb VALUES('李四',74,84,94)go

SELECT * FROMtbgo

2、使用SQL Server 2000静态SQL

--SQL SERVER 2000静态SQL。

SELECT * FROM(SELECT 姓名,课程='语文',分数=语文 FROMtbUNION ALL

SELECT 姓名,课程='数学',分数=数学 FROMtbUNION ALL

SELECT 姓名,课程='物理',分数=物理 FROMtb

) tORDER BY 姓名,CASE 课程 WHEN '语文' THEN 1 WHEN '数学' THEN 2 WHEN '物理' THEN 3 end

2、使用SQL Server 2000动态SQL

--SQL SERVER 2000动态SQL。

--调用系统表动态生态。

DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+'union all','')+'select 姓名, [课程]='

+quotename(Name,'''')+', [分数] ='+quotename(Name)+'from tb'

FROMsyscolumnsWHERE Name!='姓名' AND ID=object_id('tb')--表名tb，不包含列名为姓名的其他列

ORDER BYcolidexec(@sql+'order by 姓名')go

3、使用SQL Server 2005静态SQL

--SQL SERVER 2005动态SQL

SELECT 姓名,课程,分数 FROM tb unpivot (分数 FOR 课程 IN([语文],[数学],[物理])) t

4、使用SQL Server 2005动态SQL

--SQL SERVER 2005动态SQL

DECLARE @sql NVARCHAR(4000)SELECT @sql=isnull(@sql+',','')+quotename(Name)FROMsyscolumnsWHERE ID=object_id('tb')AND Name NOT IN('姓名')ORDER BYColidSET @sql='select 姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in('+@sql+'))b'

exec(@sql)

再次感谢！张志涛

转自：http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html