T-SQL查询：行列转换简单实例_sql


--★转换结果如上图



--创建测试标
CREATE TABLE [成绩表](
  [编号] [int] IDENTITY(1,1) NOT NULL,
  [姓名] [varchar](50) NULL,
  [语文] [numeric](5, 2) NULL,
  [数学] [numeric](5, 2) NULL,
  [英语] [numeric](5, 2) NULL
) ON [PRIMARY]


--插入数据（我的是 sqlserver 2008 r2）
INSERT INTO 成绩表([姓名],[语文],[数学],[英语])
VALUES('道一',80,100,90.8),('老二',48,56,60),('张三',88,78,90),('李四',80,90,95),('王五',67,68,56),('刘六',77,68,80)

--  SELECT * FROM 成绩表
 
--固定转换 查询 （结果如上图）
select [课程]
,SUM(case when [姓名] = '道一' then 成绩 else 0 end) '道一'
,SUM(case when [姓名] = '老二' then 成绩 else 0 end) '老二'
,SUM(case when [姓名] = '张三' then 成绩 else 0 end) '张三'
,SUM(case when [姓名] = '李四' then 成绩 else 0 end) '李四'
,SUM(case when [姓名] = '王五' then 成绩 else 0 end) '王五'
,SUM(case when [姓名] = '刘六' then 成绩 else 0 end) '刘六'
from (
select [编号],[姓名],[语文] AS '成绩','语文' AS '课程' from 成绩表
union all
select [编号],[姓名],[数学],'数学' from 成绩表
union all
select [编号],[姓名],[英语],'英语' from 成绩表
)as tb where ISNULL([编号],'')<>''
group by [课程]


--  select name from syscolumns where id=object_id('成绩表')  and CHARINDEX(name,'编号，姓名')=0

--动态转换  查询 （结果如上图）
declare @sql_1 varchar(8000)
select @sql_1=''
select @sql_1= @sql_1 + 'select [编号],[姓名],['+name+'] AS ''成绩'','''+name+''' AS ''课程'' from 成绩表 union all '
from syscolumns where id=object_id('成绩表')  and CHARINDEX(name,'编号，姓名')=0
select @sql_1= @sql_1 + ' select distinct null,null,null,null from 成绩表 '  --注意！多一行 select distinct null,null,null,null from 成绩表
print @sql_1
--execute(@sql_1)
declare @sql_2 varchar(8000)
select @sql_2='select [课程] '
select @sql_2=@sql_2 + ',SUM(case when [姓名] = '''+[姓名]+''' then 成绩 else 0 end) '''+[姓名]+''' '
from 成绩表
select @sql_2=@sql_2 + ' from ( '+@sql_1 + ')as tb where ISNULL([编号],'''')<>'''' group by [课程]'
print @sql_2
execute(@sql_2)



--再插入数据
INSERT INTO 成绩表([姓名],[语文],[数学],[英语])
VALUES('鬼七',67,68,56),('王八',77,68,80)

--再执行动态语句 （结果如下图），动态查询随表变化




T-SQL查询：行列转换简单实例_插入数据_02