server多列转⾏sql_SQLServer⾏转列，列转⾏。多⾏转成⼀
列
⼀、多⾏转成⼀列(并以","隔开)
表名：A
表数据：
想要的查询结果：isnull的用法
查询语句：
SELECT name ,
value = ( STUFF(( SELECT ',' + value
FROM A
WHERE name = Test.name
FOR
XML PATH('')
), 1, 1, '') )
FROM A AS Test
GROUP 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) FROM tb) a
OUTER APPLY (SELECT [value]=T.C.value('.','varchar(50)') FROM a.[value].nodes('/v') AS T(C)) b
1、建⽴表格
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
go
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)物理
FROM tb
GROUP 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))平均分
FROM tb
GROUP 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 课程 FROM tb)a
SET @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))平均分
FROM tb
GROUP BY 姓名)n
WHERE 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 TABLE tb
go
CREATE TABLE tb(姓名 VARCHAR(10)，语⽂ INT，数学 INT，物理 INT)
INSERT INTO tb VALUES('张三',74,83,93)
INSERT INTO tb VALUES('李四',74,84,94)
go
SELECT * FROM tb
go
2、使⽤SQL Server 2000静态SQL
--SQL SERVER 2000静态SQL。
SELECT * FROM
(
SELECT 姓名，课程='语⽂'，分数=语⽂ FROM tb
UNION ALL
SELECT 姓名，课程='数学'，分数=数学 FROM tb
UNION ALL
SELECT 姓名，课程='物理'，分数=物理 FROM tb
) t
ORDER 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 syscolumns
WHERE Name!='姓名' AND ID=object_id('tb')--表名tb，不包含列名为姓名的其他列
ORDER BY colid
exec(@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)
FROM syscolumns
WHERE ID=object_id('tb')AND Name NOT IN('姓名')
ORDER BY Colid
SET @sql='select 姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in('+@sql+'))b'
exec(@sql)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。