在论坛中出现的比较难的sql问题：6(动态行转列考试科目、排名动态列问题)

最新推荐文章于 2024-03-30 09:30:00 发布

LongRui888

最新推荐文章于 2024-03-30 09:30:00 发布

阅读量2k

点赞数

分类专栏：【MS-SQL Server论坛问题】

本文链接：https://blog.csdn.net/sqlserverdiscovery/article/details/12949749

版权

【MS-SQL Server论坛问题】专栏收录该内容

54 篇文章 5 订阅

订阅专栏

最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

下面的几个问题，都是动态行转列的问题。

数据查询，行转列的问题。

http://bbs.csdn.net/topics/390621630?page=1#post-395855019

根据数据查询得到如下数据表（tab）：
班级学号姓名科目得分班排名校排名标准分
ClassName Code Name SubjectName TotalScore ClassRank SchoolRank TValue
201班 101 张三语文 95 1 1 700
201班 102 李四语文 83 2 3 600
202班 201 张飞语文 85 1 2 700
202班 202 赵云语文 75 2 4 600

201班 101 张三数学 83 2 2 600
201班 102 李四数学 85 1 3 700
202班 201 张飞数学 95 1 1 700
202班 202 赵云数学 80 2 4 600

需要得到如下数据：
班级学号姓名语文得分班排名校排名标准分数学得分班排名校排名标准分
201班 101 张三语文 95 1 1 700 数学 83 2 2 600
201班 102 李四语文 83 2 3 600 数学 85 1 3 700
202班 201 张飞语文 85 1 2 700 数学 95 1 1 700
202班 202 赵云语文 75 2 4 600 数学 80 2 4 600

真心求SQL，或者实现方法。
科目是动态的。能实现静态转换也行。
真心谢谢。

我的解法：

if object_id('tab') is not null drop table tab
go 
create table tab
(
[班级] varchar(9),[学号] varchar(4),[姓名] varchar(4),
[科目] varchar(11),[得分] varchar(10),[班排名] varchar(9),
[校排名] varchar(10),[标准分] varchar(6)
)

insert tab
select '201班','101','张三','语文','95','1','1','700' union all
select '201班','102','李四','语文','83','2','3','600' union all
select '202班','201','张飞','语文','85','1','2','700' union all
select '202班','202','赵云','语文','75','2','4','600' union all
select '201班','101','张三','数学','83','2','2','600' union all
select '201班','102','李四','数学','85','1','3','700' union all
select '202班','201','张飞','数学','95','1','1','700' union all
select '202班','202','赵云','数学','80','2','4','600'
--------------开始查询--------------------------


declare @sql nvarchar(3000);

set @sql = '';

select @sql = 
   @sql + ',min(case when 科目=''' + 科目 + ''' then 科目 else null end) as ['+科目+ ']'+
          ',min(case when 科目=''' + 科目 + ''' then 得分 else null end) as 得分' +
          ',min(case when 科目=''' + 科目 + ''' then 班排名 else null end) as 班排名'+
          ',min(case when 科目=''' + 科目 + ''' then 校排名 else null end) as 校排名'+
          ',min(case when 科目=''' + 科目 + ''' then 标准分 else null end) as 标准分'
           
from tab
group by 科目
order by 科目 desc

select @sql = 'select 班级,学号,姓名'+@sql +
              ' from tab group by 班级,学号,姓名'

select @sql       
exec(@sql)       
/*
班级        学号   姓名   语文          得分         班排名       校排名        标准分    数学          得分         班排名       校排名        标准分
--------- ---- ---- ----------- ---------- --------- ---------- ------ ----------- ---------- --------- ---------- ------
201班      101  张三   语文          95         1         1          700    数学          83         2         2          600
201班      102  李四   语文          83         2         3          600    数学          85         1         3          700
202班      201  张飞   语文          85         1         2          700    数学          95         1         1          700
202班      202  赵云   语文          75         2         4          600    数学          80         2         4          600
警告: 聚合或其他 SET 操作消除了 Null 值。

*/

上面语句，产生的动态语句：

select 班级,学号,姓名,

       min(case when 科目='语文' then 科目 else null end) as [语文],
       min(case when 科目='语文' then 得分 else null end) as 得分,
       min(case when 科目='语文' then 班排名 else null end) as 班排名,
       min(case when 科目='语文' then 校排名 else null end) as 校排名,
       min(case when 科目='语文' then 标准分 else null end) as 标准分,
       min(case when 科目='数学' then 科目 else null end) as [数学],
       min(case when 科目='数学' then 得分 else null end) as 得分,
       min(case when 科目='数学' then 班排名 else null end) as 班排名,
       min(case when 科目='数学' then 校排名 else null end) as 校排名,
       min(case when 科目='数学' then 标准分 else null end) as 标准分 
from tab 
group by 班级,学号,姓名