类型：转载 责任编辑：asp.net 日期：2007/05/23
热门软件下载：
TABLE:
ID BT SJ
1 满意 2004-10-01
1 不满意 2004-09-01
1 一般 2004-08-01
2 满意 2004-10-01
2 不满意 2004-09-01
2 一般 2004-08-01
最后生成报表
ID 满意 不满意 一般
1 2004-10-01 2004-09-01 2004-08-01
2 2004-10-01 2004-09-01 2004-08-01
网友回答：
select [ID],(select sj from test as t where t.id=s.id and t.bt=满意) as 满意,(select sj from test as t where t.id=s.id and t.bt=不满意) as 不满意,(select sj from test as t where t.id=s.id and t.bt=一般) as 一般 from test as s group by s.id
select id,
满意=max(case when bt=满意 then sj end)
,不满意=max(case when bt=不满意 then sj end)
,一般=max(case when bt=一般 then sj end)
from tb
group by id
--测试
create table tb(ID int,BT varchar(6),SJ varchar(10) )
insert into tb
select 1,满意,2004-10-01 union
select 1,不满意,2004-09-01 union
select 1,一般, 2004-08-01 union
select 2,满意, 2004-10-01 union
select 2,不满意,2004-09-01 union
select 2,一般, 2004-08-01
---查询
select id,
满意=max(case when bt=满意 then sj end)
,不满意=max(case when bt=不满意 then sj end)
,一般=max(case when bt=一般 then sj end)
from tb
group by id
--删除测试表
drop table tb
--结果：
id 满意 不满意 一般
----------- ---------- ---------- ----------
1 2004-10-01 2004-09-01 2004-08-01
2 2004-10-01 2004-09-01 2004-08-01
select a.[id],a.sj as 满意,b.sj as 不满意,c.sj as 一般
from
(select [id],sj from table1 where bt=满意) a
full join
(select [id],sj from table1 where bt=不满意) b
on a.[id2]=b.[id2]
full join
(select [id2],sj from table1 where bt=一般) c
on a.[id2]=c.[id2]