create table kc(kcname varchar(20), sd1 int, sd2 int, sd3 int, sd4 int, sd5 int, sd6 int, sd7 int) insert into kc select '语文',1, 2, 3, 4, 5, 6, null union all select '数学', 13, 12, 11, 10, 9, 8, 7 union all select '英语', 14, 15,null, null, null, null, null union all select '美术', 16, 17,null, null, null, null, null union all select '计算机', 18,null, null, null, null, null,null union all select '体育', 19, 20,null, null, null, null, null union all select '科学', 21, 22,null, null, null, null, null union all select '实践', 23, null, null, null, null, null,null union all select '社会', 24, 25,null, null, null, null, null union all select '音乐', 26, 27,null, null, null, null, null union all select '作文', 28, 29,null, null, null, null, null union all select '班会', 30,null, null, null, null, null,nullwith t as ( select kcname,sd = sd1 from kc where sd1 is not null union all select kcname,sd = sd2 from kc where sd2 is not null union all select kcname,sd = sd3 from kc where sd3 is not null union all select kcname,sd = sd4 from kc where sd4 is not null union all select kcname,sd = sd5 from kc where sd5 is not null union all select kcname,sd = sd6 from kc where sd6 is not null union all select kcname,sd = sd7 from kc where sd7 is not null ) ,w as ( select dayname = '星期一', daynum = 0 union all select dayname = '星期二', daynum = 1 union all select dayname = '星期三', daynum = 2 union all select dayname = '星期四', daynum = 3 union all select dayname = '星期五', daynum = 4 ) ,r as ( select t.kcname,orderid = case sd%6 when 0 then 6 else sd%6 end,w.dayname from t, w where (t.sd-1)/6 = w.daynum ) select * from r pivot (max(kcname) for dayname in ([星期一],[星期二],[星期三],[星期四],[星期五])) a/* orderid 星期一 星期二 星期三 星期四 星期五 ----------- -------------------- -------------------- -------------------- -------------------- -------------------- 1 语文 数学 数学 体育 社会 2 语文 数学 英语 体育 音乐 3 语文 数学 英语 科学 音乐 4 语文 数学 美术 科学 作文 5 语文 数学 美术 实践 作文 6 语文 数学 计算机 社会 班会(6 行受影响) */楼主,请记得结贴啊
J的下面是K,S的下面是T,U
create table kc(kcname varchar(20), sd1 int, sd2 int, sd3 int, sd4 int, sd5 int, sd6 int, sd7 int)
insert into kc
select '语文',1, 2, 3, 4, 5, 6, null union all
select '数学', 13, 12, 11, 10, 9, 8, 7 union all
select '英语', 14, 15,null, null, null, null, null union all
select '美术', 16, 17,null, null, null, null, null union all
select '计算机', 18,null, null, null, null, null,null union all
select '体育', 19, 20,null, null, null, null, null union all
select '科学', 21, 22,null, null, null, null, null union all
select '实践', 23, null, null, null, null, null,null union all
select '社会', 24, 25,null, null, null, null, null union all
select '音乐', 26, 27,null, null, null, null, null union all
select '作文', 28, 29,null, null, null, null, null union all
select '班会', 30,null, null, null, null, null,nullwith t as
(
select kcname,sd = sd1 from kc where sd1 is not null union all
select kcname,sd = sd2 from kc where sd2 is not null union all
select kcname,sd = sd3 from kc where sd3 is not null union all
select kcname,sd = sd4 from kc where sd4 is not null union all
select kcname,sd = sd5 from kc where sd5 is not null union all
select kcname,sd = sd6 from kc where sd6 is not null union all
select kcname,sd = sd7 from kc where sd7 is not null
)
,w as
(
select dayname = '星期一', daynum = 0 union all
select dayname = '星期二', daynum = 1 union all
select dayname = '星期三', daynum = 2 union all
select dayname = '星期四', daynum = 3 union all
select dayname = '星期五', daynum = 4
)
,r as
(
select t.kcname,orderid = case sd%6 when 0 then 6 else sd%6 end,w.dayname
from t, w where (t.sd-1)/6 = w.daynum
)
select * from r pivot (max(kcname) for dayname in ([星期一],[星期二],[星期三],[星期四],[星期五])) a/*
orderid 星期一 星期二 星期三 星期四 星期五
----------- -------------------- -------------------- -------------------- -------------------- --------------------
1 语文 数学 数学 体育 社会
2 语文 数学 英语 体育 音乐
3 语文 数学 英语 科学 音乐
4 语文 数学 美术 科学 作文
5 语文 数学 美术 实践 作文
6 语文 数学 计算机 社会 班会(6 行受影响)
*/楼主,请记得结贴啊