SQL Server 行轉列，列轉行。多行轉成一列

標籤：title   ima   gpo   轉行   distinct   max   str   png   order   

一、多行轉成一列（並以","隔開）

表名：A

表資料：

想要的查詢結果：

查詢語句：

SELECT  name ,        value = ( STUFF(( SELECT    ‘,‘ + value                          FROM      A                          WHERE     name = Test.name                        FOR                          XML PATH(‘‘)                        ), 1, 1, ‘‘) )FROM    A AS TestGROUP BY name;

PS：STUFF語句就是為了去掉第一個【逗號】

附STUFF用法：（從原字元的第二個開始共三個字元替換為後面的字元）

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

查詢結果：aijklmnef

 二、一列轉成多行

表名：tb

表資料：

想要的結果：

查詢語句：

SELECT a.[name],b.[value]FROM (SELECT [name],[value]=CAST(‘<v>‘+REPLACE([value],‘,‘,‘</v><v>‘)+‘</v>‘ AS xml) FROM tb) 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 TABLE tbgoCREATE 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)goSELECT * 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)物理FROM tbGROUP 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=‘‘  --初始設定變數@sqlSELECT @[email protected]+‘,‘+課程 FROM tb GROUP BY 課程 --變數多值賦值SET @sql=stuff(@sql,1,1,‘‘)--去掉首個‘,‘SET @sql=‘select * from tb pivot (max(分數) for 課程 in (‘[email protected]+‘))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 (‘[email protected]+‘))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))平均分FROM tbGROUP BY 姓名

2、使用SQL Server 2000動態SQL

DECLARE @sql VARCHAR(500)SET @sql=‘select 姓名‘SELECT @[email protected]+‘,max(case 課程 when ‘‘‘+課程+‘‘‘ then 分數 else 0 end)[‘+課程+‘]‘from(SELECT DISTINCT 課程 FROM tb)aSET @[email protected]+‘,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))平均分FROM tbGROUP BY 姓名)nWHERE m.姓名=n.姓名

4、使用SQL Server 2005動態SQL

--使用stuff()--DECLARE @sql VARCHAR(8000)SET @sql=‘‘  --初始設定變數@sqlSELECT @[email protected]+‘,‘+課程 FROM tb GROUP BY 課程 --變數多值賦值--同select @sql = @sql + ‘,‘+課程from (select distinct課程from tb)aSET @sql=stuff(@sql,1,1,‘‘)--去掉首個‘,‘SET @sql=‘select m.* , n.總分,n.平均分 from(select * from (select * from tb) a pivot (max(分數) for 課程 in (‘[email protected]+‘)) b) m ,(select 姓名,sum(分數) 總分, cast(avg(分數*1.0) as decimal(18,2))平均分 from tb group by 姓名) nwhere 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 (‘[email protected]+‘)) b) m ,(select 姓名,sum(分數)總分, cast(avg(分數*1.0) as decimal(18,2))平均分 from tb group by 姓名) nwhere m.姓名= n.姓名‘exec(@sql)

五、列轉行

1、建立表格

IF OBJECT_ID(‘tb‘)IS NOT NULL DROP TABLE tbgoCREATE TABLE tb(姓名 VARCHAR(10),語文 INT,數學 INT,物理 INT)INSERT INTO tb VALUES(‘張三‘,74,83,93)INSERT INTO tb VALUES(‘李四‘,74,84,94)goSELECT * FROM tbgo

2、使用SQL Server 2000靜態SQL

--SQL SERVER 2000靜態SQL。SELECT * FROM( SELECT 姓名,課程=‘語文‘,分數=語文 FROM tb UNION ALL SELECT 姓名,課程=‘數學‘,分數=數學 FROM tb UNION ALL SELECT 姓名,課程=‘物理‘,分數=物理 FROM tb) 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‘FROM syscolumnsWHERE Name!=‘姓名‘ AND ID=object_id(‘tb‘)--表名tb，不包含列名為姓名的其他列ORDER BY colidexec(@sql+‘ order by 姓名‘)go

3、使用SQL Server 2005靜態SQL

--SQL SERVER 2005動態SQLSELECT 姓名,課程,分數 FROM tb unpivot (分數 FOR 課程 IN([語文],[數學],[物理])) t

4、使用SQL Server 2005動態SQL

--SQL SERVER 2005動態SQLDECLARE @sql NVARCHAR(4000)SELECT @sql=isnull(@sql+‘,‘,‘‘)+quotename(Name)FROM syscolumnsWHERE ID=object_id(‘tb‘)AND Name NOT IN(‘姓名‘)ORDER BY ColidSET @sql=‘select 姓名,[課程],[分數] from tb unpivot ([分數] for [課程] in(‘[email protected]+‘))b‘exec(@sql)

 

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

SQL Server 行轉列，列轉行。多行轉成一列