--================== 行转列 ====================================================
DROP table #student
CREATE TABLE #student (stdname nvarchar(10),stdsubject nvarchar(10),result int)
INSERT INTO #student VALUES ('张三','语文',80)
INSERT INTO #student values ('张三','数学',90)
INSERT INTO #student VALUES ('张三','物理',85)
INSERT INTO #student VALUES ('李四','语文',85)
INSERT INTO #student values ('李四','数学',92)
INSERT INTO #student VALUES ('李四','物理',82)
INSERT INTO #student VALUES ('李四','化学',82)
------ 行
SELECT * FROM #student
------ 列
select stdname, isnull(sum(case stdsubject when '化学' then Result end),0) [化学], isnull(sum(case stdsubject when '数学' then Result end),0) [数学], isnull(sum(case stdsubject when '物理' then Result end),0) [物理], isnull(sum(case stdsubject when '语文' then Result end),0) [语文] from #student group by stdname
--重构
declare @sql varchar(1000)set @sql='Select stdname, '
select @sql=@sql+' sum(case when stdsubject='''+stdsubject+''' then result else 0 end) as ['+stdsubject+'],'from (select distinct stdsubject from #student)Temp
set @sql=LEFT(@sql,len(@sql)-1)+' from #student Group by stdname'
print @sqlexec(@sql)
--================== 列转行 ====================================================
DROP table #student2
CREATE TABLE #student2 (stdname nvarchar(10),化学 int,数学 int,物理 int ,语文 int )
INSERT INTO #student2 VALUES ('李四',82,92,82,85) INSERT INTO #student2 VALUES ('张三',0,90,85,80)
--列
SELECT * FROM #student2
--行
SELECT'李四'as stdname,stdname='化学', 化学 as result from #student2 where stdname='李四'
union all SELECT'李四'as stdname,stdname='数学', 数学 as result from #student2 where stdname='李四'
union all SELECT'李四'as stdname,stdname='物理', 物理 as result from #student2 where stdname='李四'
union all SELECT'李四'as stdname,stdname='语文', 语文 as result from #student2 where stdname='李四'
union all SELECT'张三'as stdname,stdname='化学', 化学 as result from #student2 where stdname='张三'
union all SELECT'张三'as stdname,stdname='数学', 数学 as result from #student2 where stdname='张三'
union all SELECT'张三'as stdname,stdname='物理', 物理 as result from #student2 where stdname='张三'
union all SELECT'张三'as stdname,stdname='语文', 语文 as result from #student2 where stdname='张三'
--重构
select [name] into #tmpCloumnsfrom tempdb.dbo.syscolumnswhere id=object_id('tempdb.dbo.#student2')and [name]<>'stdname'
--select * from #tmpCloumns
declare @strSql nvarchar(800)select @strSql=''select @strSql=@strSql+'union all'+char(10)+char(13)+'select [stdname],'''+[name]+''' as [科目],['+[name]+']'+char(10)+char(13)+'from [#student2]'+char(10)+char(13)from #tmpCloumns
select @strSql=substring(@strSql,11,len(@strSql))+'order by stdname,[科目]'print @strSqlexec(@strsql)