SQL Server 行列转换--经典面试题

今天在论坛上看了个比较经典的行列转换，用T-SQL写的，正好学习学习思路，在原帖的基础上稍微改动了些，不过整体思路差不多。

表一：                                                                     

表二：

 

代码：

表一

   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 )
   INSERT INTO student VALUES ( ' 李四 ' , ' 化学 ' , 82 )
   SELECT  *  FROM student

表二

 CREATE TABLE student2 (stdname nvarchar(10),化学 int,数学 int,物理 int ,语文 int )
 INSERT INTO student2 VALUES ('李四',164,92,82,85)
 INSERT INTO student2 VALUES ('张三',0,90,85,80)

 SELECT * FROM student2

 

 

首先将表一转换成表二：

代码：

  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(4000)
set @sql='select stdname'
select @sql=@sql+ 
  ',isnull(sum( case  stdsubject when  '''+stdsubject+'''  then Result end), 0 ) '''+stdsubject+ ''''
 from (select distinct stdsubject from student )a
select @sql=@sql+ ' from student  group by stdname '
print @sql
exec (@sql)

 

将表二转换成表一：

代码：

SELECT'李四'as stdname,subject='化学', 化学 as result from student2 where stdname='李四'
union all
SELECT'李四'as stdname,subject='数学', 数学 as result from student2 where stdname='李四'
union all
SELECT'李四'as stdname,subject='物理', 物理 as result from student2 where stdname='李四'
union all
SELECT'李四'as stdname,subject='语文', 语文 as result from student2 where stdname='李四'
union all
SELECT'张三'as stdname,subject='化学', 化学 as result from student2 where stdname='张三'
union all
SELECT'张三'as stdname,subject='数学', 数学 as result from student2 where stdname='张三'
union all
SELECT'张三'as stdname,subject='物理', 物理 as result from student2 where stdname='张三'
union all
SELECT'张三'as stdname,subject='语文', 语文 as result from student2 where stdname='张三'

 

重新构成：

declare @sql varchar(4000)
set @sql=''
SELECT  @sql=@sql+'
SELECT '''+stdname+''',''化学''as [subject],化学 as [result] from student2 where stdname='''+stdname+''' union all
SELECT '''+stdname+''',''数学''as [subject],数学 as [result] from student2 where stdname='''+stdname+''' union all
SELECT '''+stdname+''',''物理''as [subject],物理 as [result] from student2 where stdname='''+stdname+''' union all
SELECT '''+stdname+''',''语文''as [subject],语文 as [result] from student2 where stdname='''+stdname+''' union all'
FROM (SELECT stdname FROM student2) as a
 SELECT @sql = LEFT(@sql,LEN(@sql) - 11)
print @sql

 

更加灵活的是：

declare @strsql varchar(4000)
   select @strSql = ''
   select @strSql = @strSql + ' union all 
                     select [stdname], ''' + [name] + '''  as [科目],[' + [name] + '] ' +
                   ' from [student2]
     ' 
  from tempcloumns
select @strSql = substring(@strSql, 11 ,len(@strSql)) + ' order by stdname,[科目] '
print @strsql