求取数的脚本，不复杂，但是很难解决方案

求取数的脚本，不复杂，但是很难
数据表格式如下：
姓名     科目1   科目2   科目3  科目4  科目5  成绩1  成绩2  成绩3  成绩4  成绩5
张三      物理                 英语                地理    66                   77                   45
李四                  物理     数学                历史                55       85                   76  
------------------------------------------------------------------------------------------------------------
求语句：求语句或方法能列出每个人的考试科目和考试成绩
注：1、学科名称 不固定在哪个科目下  例如 张三的物理在科目1  李四的物理在科目2
        2、有的科目为空，为空的不取，成绩对应科目，例如张三的 成绩1对应的是科目1，即物理成绩为66
-------------------------------------------------------------------------------------------------------------
最终结果：
张三：
物理    66
英语    77 
地理    45
李四：
物理    55
数学    85
历史    76

------解决方案--------------------
不动脑的做法是每次抽对应不为null的两列然后做union all
------解决方案--------------------
就用1楼的方法吧。

select 姓名, 科目1 科目, 成绩1 成绩 from 表 where 科目1<>''
union all
select 姓名, 科目2 科目, 成绩2 成绩 from 表 where 科目2<>''
union all
select 姓名, 科目3 科目, 成绩3 成绩 from 表 where 科目3<>''
union all
select 姓名, 科目4 科目, 成绩4 成绩 from 表 where 科目4<>''
union all
select 姓名, 科目5 科目, 成绩5 成绩 from 表 where 科目5<>''

------解决方案--------------------

目测需要使用union拼接

--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
 go
 create table [ta] ([姓名] varchar(4),[科目1] varchar(4),[科目2] varchar(4),[科目3] varchar(4),[科目4] varchar(4),[科目5] varchar(4),[成绩1] int,[成绩2] int,[成绩3] int,[成绩4] int,[成绩5] int)
insert into [ta]
select '张三','物理',null,'英语',null,'地理',66,null,77,null,45 union all
select '李四',null,'物理','数学',null,'历史',null,55,85,null,76

select * from [ta]

select 姓名,科目1,成绩1
from ta
where 科目1 is not null
union 
select 姓名,科目2,成绩2
from ta
where 科目2 is not null
union 
select 姓名,科目3,成绩3
from ta
where 科目3 is not null
union 
select 姓名,科目4,成绩4
from ta
where 科目4 is not null
union 
select 姓名,科目5,成绩5
from ta
where 科目5 is not null

李四	历史	76
李四	数学	85
李四	物理	55
张三	地理	45
张三	物理	66
张三	英语	77

------解决方案--------------------

引用:

Quote: 引用:

不动脑的做法是每次抽对应不为null的两列然后做union all

我这只列了这5个科目 其实还有好多 要是这么union all下去  费劲死了

这种不定字段的，除了动态拼接，就只有手动写上所有union  取不到巧的
------解决方案--------------------

if object_id('Tempdb..#t') is not null drop table #t
 
create table #t(
id int identity(1,1) not null,
[姓名] nvarchar(10) null,
[科目1] nvarchar(10) null,
[科目2] nvarchar(10) null,
[科目3] nvarchar(10) null,
[科目4] nvarchar(10) null,
[科目5] nvarchar(10) null,
[成绩1] int null,
[成绩2] int null,
[成绩3] int null,
[成绩4] int null,
[成绩5] int null
)
 
Insert Into #t
select '张三','物理',null, '英语',null,'地理',66,null,77,null,45 union all
select '李四',null,'物理','数学',null,'历史',null,55,85,null,76 

 
;with cte(xm,km,cj) as(
select 姓名,科目1,成绩1 from #t union all
select 姓名,科目2,成绩2 from #t union all
select 姓名,科目3,成绩3 from #t union all
select 姓名,科目4,成绩4 from #t union all
select 姓名,科目5,成绩5 from #t  
 )
select * from cte where km is not null and cj is not null
-----------------
---结果