写成固定的
select 姓名,
"1"=(select 警告 from a where substring (日期,1,7)='2004-01' and 姓名=t1.姓名),
"2"=(select 警告 from a where substring (日期,1,7)='2004-02' and 姓名=t1.姓名),
"3"=(select 警告 from a where substring (日期,1,7)='2004-03' and 姓名=t1.姓名),
"4"= (select 警告 from a where substring (日期,1,7)='2004-04' and 姓名=t1.姓名),
"5"=(select 警告 from a where substring (日期,1,7)='2004-05' and 姓名=t1.姓名),
"6"= (select 警告 from a where substring (日期,1,7)='2004-06' and 姓名=t1.姓名),
"7"= (select 警告 from a where substring (日期,1,7)='2004-07' and 姓名=t1.姓名),
"8"= (select 警告 from a where substring (日期,1,7)='2004-08' and 姓名=t1.姓名),
"9"= (select 警告 from a where substring (日期,1,7)='2004-09' and 姓名=t1.姓名),
"10"= (select 警告 from a where substring (日期,1,7)='2004-10' and 姓名=t1.姓名),
"11"= (select 警告 from a where substring (日期,1,7)='2004-11' and 姓名=t1.姓名),
"12"=(select 警告 from a where substring (日期,1,7)='2004-12' and 姓名=t1.姓名)
from A t1 group by 姓名要是写成灵活的,需要用存储过程动态语句
select 姓名,
"1"=(select 警告 from a where substring (日期,1,7)='2004-01' and 姓名=t1.姓名),
"2"=(select 警告 from a where substring (日期,1,7)='2004-02' and 姓名=t1.姓名),
"3"=(select 警告 from a where substring (日期,1,7)='2004-03' and 姓名=t1.姓名),
"4"= (select 警告 from a where substring (日期,1,7)='2004-04' and 姓名=t1.姓名),
"5"=(select 警告 from a where substring (日期,1,7)='2004-05' and 姓名=t1.姓名),
"6"= (select 警告 from a where substring (日期,1,7)='2004-06' and 姓名=t1.姓名),
"7"= (select 警告 from a where substring (日期,1,7)='2004-07' and 姓名=t1.姓名),
"8"= (select 警告 from a where substring (日期,1,7)='2004-08' and 姓名=t1.姓名),
"9"= (select 警告 from a where substring (日期,1,7)='2004-09' and 姓名=t1.姓名),
"10"= (select 警告 from a where substring (日期,1,7)='2004-10' and 姓名=t1.姓名),
"11"= (select 警告 from a where substring (日期,1,7)='2004-11' and 姓名=t1.姓名),
"12"=(select 警告 from a where substring (日期,1,7)='2004-12' and 姓名=t1.姓名)
from A t1 group by 姓名要是写成灵活的,需要用存储过程动态语句
Max( case month(日期) when 1 then (case 警告 when 1 then '1' else '0' end) else '' end) as '1',
Max( case month(日期) when 2 then (case 警告 when 1 then '1' else '0' end) else '' end) as '2',
Max( case month(日期) when 3 then (case 警告 when 1 then '1' else '0' end) else '' end) as '3',
Max( case month(日期) when 4 then (case 警告 when 1 then '1' else '0' end) else '' end) as '4',
Max( case month(日期) when 5 then (case 警告 when 1 then '1' else '0' end) else '' end) as '5',
Max( case month(日期) when 6 then (case 警告 when 1 then '1' else '0' end) else '' end) as '6',
Max( case month(日期) when 7 then (case 警告 when 1 then '1' else '0' end) else '' end) as '7',
Max( case month(日期) when 8 then (case 警告 when 1 then '1' else '0' end) else '' end) as '8',
Max( case month(日期) when 9 then (case 警告 when 1 then '1' else '0' end) else '' end) as '9',
Max( case month(日期) when 10 then (case 警告 when 1 then '1' else '0' end) else '' end) as '10',
Max( case month(日期) when 11 then (case 警告 when 1 then '1' else '0' end) else '' end) as '11',
Max( case month(日期) when 12 then (case 警告 when 1 then '1' else '0' end) else '' end) as '12'
from A
group by year(日期),姓名
go
2004 李四 1 0 0 0 1 1 1
2004 王五 0 1
2003 张三 0 0 1 0 1
2004 张三 0 0 0 1 0 1 0 0 0 1 0 0可以结了!