麦林 发表于 2007/11/22 17:32:20 |
原始表数据为: pid document p001 1号楼第一季度文档 p001 1号楼第二季度文档 p001 1号楼第三季度文档 p001 1号楼第四季度文档 p002 2号楼第一季度文档 p002 2号楼第二季度文档 p002 2号楼第三季度文档 p003 3号楼第一季度文档 p003 3号楼第二季度文档 p003 3号楼第三季度文档 p003 3号楼第四季度文档
建表所用的语句: CREATE TABLE [dbo].[quest] ( [pid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL , [document] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO insert into quest(pid,document) values( 'p001 ', '1号楼第一季度文档 ') insert into quest(pid,document) values( 'p001 ', '1号楼第二季度文档 ') insert into quest(pid,document) values( 'p001 ', '1号楼第三季度文档 ') insert into quest(pid,document) values( 'p001 ', '1号楼第四季度文档 ') insert into quest(pid,document) values( 'p002 ', '2号楼第一季度文档 ') insert into quest(pid,document) values( 'p002 ', '2号楼第二季度文档 ') insert into quest(pid,document) values( 'p002 ', '2号楼第三季度文档 ') insert into quest(pid,document) values( 'p003 ', '3号楼第一季度文档 ') insert into quest(pid,document) values( 'p003 ', '3号楼第二季度文档 ') insert into quest(pid,document) values( 'p003 ', '3号楼第三季度文档 ') insert into quest(pid,document) values( 'p003 ', '3号楼第四季度文档 ')
实现结果：/*p001 1号楼第一季度文档 1号楼第二季度文档 1号楼第三季度文档 1号楼第四季度文档 p002 2号楼第一季度文档 2号楼第二季度文档 2号楼第三季度文档 NULL p003 3号楼第一季度文档 3号楼第二季度文档 3号楼第三季度文档 3号楼第四季度文档 */
实现sql语句：方法一：declare @i int,@sql varchar(8000)select @i = max(document), @sql ='' from (select count(document) as document from quest group by pid) t while @i>0 begin select @sql=',document'+rtrim(@i)+'= max(case i when '+rtrim(@i)+'then document end)'+@sql,@i=@i-1 endset @sql='select pid'+ @sql +'from (select *,(select count(*) from quest where pid=a.pid and (charindex
(substring(document,5,1),''一二三四五六七八九十'')<=charindex(substring(a.document,5,1), ''一二三四五六七
八九十''))) as i from quest a)t group by pid' exec(@sql)
方法二：select distinct a.pid ,b.document document1 ,c.document document2 ,d.document document3 ,f.document
document4 from quest a right join (select pid ,document from quest where substring(document,5,1) = '一') b on b.pid = a.pidleft join (select pid ,document from quest where substring(document,5,1) = '二') c on c.pid = a.pidleft join (select pid ,document from quest where substring(document,5,1) = '三') d on d.pid = a.pidleft join (select pid ,document from quest where substring(document,5,1) = '四') f on f.pid = a.pid
|
.: 
