固定行转列 select 教师号,sum(case when 星期号 = 1 then 1 else 0 end) as 星期一 ,sum(case when 星期号 = 2 then 1 else 0 end) as 星期二 ,sum(case when 星期号 = 3 then 1 else 0 end) as 星期三 ,sum(case when 星期号 = 4 then 1 else 0 end) as 星期四 ,sum(case when 星期号 = 5 then 1 else 0 end) as 星期五 ,sum(case when 星期号 = 6 then 1 else 0 end) as 星期六 ,sum(case when 星期号 = 7 then 1 else 0 end) as 星期日 from teacher group by 教师号
if OBJECT_ID('teacher') is not null drop table teacher go create table teacher(教师号 int,星期号 int,是否有课 varchar(2)) insert into teacher select 1, 2, '有' union all select 1, 3, '有' union all select 2, 1, '有' union all select 3, 2, '有' union all select 1, 2, '有'
select 教师号,星期一= sum(case 星期号 when 1 then case 是否有课 when '有' then 1 else 0 end else 0 end), 星期二= sum(case 星期号 when 2 then case 是否有课 when '有' then 1 else 0 end else 0 end), 星期三= sum(case 星期号 when 3 then case 是否有课 when '有' then 1 else 0 end else 0 end) from teacher group by 教师号
教师号 星期一 星期二 星期三 1 0 2 1 2 1 0 0 3 0 1 0
不好意思,还有一个条件没注意。select 教师号,sum(case when 星期号 = 1 and 是否有课='有' then 1 else 0 end) as 星期一 ,sum(case when 星期号 = 2 then 1 and 是否有课='有' else 0 end) as 星期二 ,sum(case when 星期号 = 3 then 1 and 是否有课='有' else 0 end) as 星期三 ,sum(case when 星期号 = 4 then 1 and 是否有课='有' else 0 end) as 星期四 ,sum(case when 星期号 = 5 then 1 and 是否有课='有' else 0 end) as 星期五 ,sum(case when 星期号 = 6 then 1 and 是否有课='有' else 0 end) as 星期六 ,sum(case when 星期号 = 7 then 1 and 是否有课='有' else 0 end) as 星期日 from teacher group by 教师号
IF OBJECT_ID('TEACHER') IS NOT NULL DROP TABLE TEACHER GO CREATE TABLE TEACHER ( TID INT, DAY INT, FLAG INT ) INSERT INTO TEACHER SELECT 1,2,1 UNION ALL SELECT 1,3,1 UNION ALL SELECT 2,1,1 UNION ALL SELECT 3,2,1 UNION ALL SELECT 1,2,1SELECT TID ,[1] as '星期一' ,[2] as '星期二' ,[3] as '星期三' ,[4] as '星期四' ,[5] as '星期五' ,[6] as '星期六' ,[6] as '星期日' FROM (SELECT * FROM TEACHER WHERE FLAG=1) AS T PIVOT (COUNT(FLAG) FOR DAY IN([1],[2],[3],[4],[5],[6],[7]))AS P
select 教师号,sum(case 星期号 when 1 then 1 else 0 end) as 星期一, sum(case 星期号 when 2 then 1 else 0 end) as 星期二, sum(case 星期号 when 3 then 1 else 0 end) as 星期三 from teacher group by 教师号
select 教师号,sum(case when 星期号 = 1 then 1 else 0 end) as 星期一
,sum(case when 星期号 = 2 then 1 else 0 end) as 星期二
,sum(case when 星期号 = 3 then 1 else 0 end) as 星期三
,sum(case when 星期号 = 4 then 1 else 0 end) as 星期四
,sum(case when 星期号 = 5 then 1 else 0 end) as 星期五
,sum(case when 星期号 = 6 then 1 else 0 end) as 星期六
,sum(case when 星期号 = 7 then 1 else 0 end) as 星期日
from teacher
group by 教师号
if OBJECT_ID('teacher') is not null
drop table teacher
go
create table teacher(教师号 int,星期号 int,是否有课 varchar(2))
insert into teacher
select 1, 2, '有' union all
select 1, 3, '有' union all
select 2, 1, '有' union all
select 3, 2, '有' union all
select 1, 2, '有'
select 教师号,星期一= sum(case 星期号 when 1 then case 是否有课 when '有' then 1
else 0 end
else 0 end),
星期二= sum(case 星期号 when 2 then case 是否有课 when '有' then 1
else 0 end
else 0 end),
星期三= sum(case 星期号 when 3 then case 是否有课 when '有' then 1
else 0 end
else 0 end)
from teacher
group by 教师号
教师号 星期一 星期二 星期三
1 0 2 1
2 1 0 0
3 0 1 0
,sum(case when 星期号 = 2 then 1 and 是否有课='有' else 0 end) as 星期二
,sum(case when 星期号 = 3 then 1 and 是否有课='有' else 0 end) as 星期三
,sum(case when 星期号 = 4 then 1 and 是否有课='有' else 0 end) as 星期四
,sum(case when 星期号 = 5 then 1 and 是否有课='有' else 0 end) as 星期五
,sum(case when 星期号 = 6 then 1 and 是否有课='有' else 0 end) as 星期六
,sum(case when 星期号 = 7 then 1 and 是否有课='有' else 0 end) as 星期日
from teacher
group by 教师号
IF OBJECT_ID('TEACHER') IS NOT NULL
DROP TABLE TEACHER
GO
CREATE TABLE TEACHER
(
TID INT,
DAY INT,
FLAG INT
)
INSERT INTO TEACHER
SELECT 1,2,1 UNION ALL
SELECT 1,3,1 UNION ALL
SELECT 2,1,1 UNION ALL
SELECT 3,2,1 UNION ALL
SELECT 1,2,1SELECT TID
,[1] as '星期一'
,[2] as '星期二'
,[3] as '星期三'
,[4] as '星期四'
,[5] as '星期五'
,[6] as '星期六'
,[6] as '星期日'
FROM (SELECT * FROM TEACHER WHERE FLAG=1) AS T
PIVOT (COUNT(FLAG) FOR DAY IN([1],[2],[3],[4],[5],[6],[7]))AS P
select 教师号,sum(case 星期号 when 1 then 1 else 0 end) as 星期一,
sum(case 星期号 when 2 then 1 else 0 end) as 星期二,
sum(case 星期号 when 3 then 1 else 0 end) as 星期三
from teacher
group by 教师号