假使如果库名为urTable,最多只4个字段decalre @t TABLE(seq int IDENTITY(1,1),id int,jcrq datetime)---jcrq type is your table type insert @t(id,jcrq) select id,jcqq from urTableselect DISTINCT id,jcrq into #jcrq from urTable group by idselect @t.id,@t.jcrq into #jcrq1 from #jcrq,@t where @t.id=#jcrq.id and @t.seq=(selcet seq from @t where #[email protected])+1select @t.id,@t.jcrq into #jcrq2 from #jcrq1,@t where @t.id=#jcrq1.id and @t.seq=(selcet seq from @t where #[email protected])+1select @t.id,@t.jcrq into #jcrq3 from #jcrq,@t where @t.id=#jcrq2.id and @t.seq=(selcet seq from @t where #[email protected])+1select id,jcrq from #jcrq inner join #jcrq1 on #[email protected] inner join #jcrq2 on #[email protected] inner join #jcrq3 on #[email protected] ----如果字段数不定,则可用循环的动态SQL语句
明白你的意思有难度可以这样考虑。首先id按分组,然后找出重复最多的假设为n,动态创建一个临时表该表包括 id ,jcrq ,jcrq1,.....jcrqn-1。然后使用游标把数据根据分组结果每组中id的个数,从原表中把jcrq ,jcrq1,.....jcrqn-1填充。估计这个需要存储过程,给你个思路,不知道还有没有其他好的解决办法:)重点关注
/***采用游标技术来实现你的要求,建立了临时表tempdata,该表的字段数目根据你Id重复情况来动态生成 比如最大100行id重复,则生成101列(id,jcrq0,jcrq2......jcrq99),你的原始表为urtable(id int ,jcrq int) 你可以根据具体情况来替换列的名字和类型 可能有特殊情况没有考虑周到,我用我的表测试通过,还有注意sql server支持的最大字段数目应该有限制
/**** 变量声明******/ declare @Maxnumber int declare @loop int declare @id int declare @Curr int declare @groupRows int declare @jcrq int declare @updateStr varchar(100) declare @loopi int declare @CrearetableStr varchar(500)/** 如果临时表存在删除*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tempData] set @loop =0 set @loopi =0 /**得到可能的最大列,如果有100条id重复的话,则建立jcrq0到jcrq99**/ select @Maxnumber = count(id ) from urtable group by id order by count(id ) if @Maxnumber is null select @Maxnumber = 0/**生成动态Sql语句串,用处为动态建立需要字段数目的表tempData**/ select @CrearetableStr ='create table tempData (id int ' while (@loop<@Maxnumber ) begin select @CrearetableStr = @CrearetableStr + ', jcrq'+ ltrim(rtrim(str(@loop))) + ' int' select @loop =@loop+1 end select @CrearetableStr = @CrearetableStr + ')'/**动态生成tempData,根据生成的动态sql 语句***/exec(@CrearetableStr)--在tempData表中插入不重复的id,其余字段为空insert tempdata(id) select distinct(id) from urtable----获得分组结果的组数这里使用了@@rowcount全局变量,可能不妥 SELECT id,count(id) FROM urtable group by id select @groupRows = @@rowcountset @loop =0 /**开始更新各行的相应数目的jcrq字段***/ /** 在使用游标时没有根据@@FETCH_STATUS来判断是否取完,因为用到了两个游标,所以无法使用该 全局变量来判断外层用@groupRows,内层根据外层得到的行重复的数目@Curr来判断循环条件*/ DECLARE Groupcur CURSOR FOR SELECT id,count(id) FROM urtable group by id /**第一个游标类来得到ID重复的情况,即每个ID有几行重复**/OPEN Groupcur while @loop<@grouprowS begin FETCH NEXT FROM Groupcur INTO @id,@Curr ---第二个游标根据给定行的重复情况来决定更新tempData对应行jcrq的个数及内容 DECLARE inGroupcur CURSOR FOR SELECT jcrq FROM urtable where id=@Id OPEN inGroupcur set @loopi =0 while @loopi<@curr begin FETCH NEXT FROM inGroupcur INTO @jcrq if @jcrq is null select @jcrq= 0 --生成更新语句 select @updateStr = 'update tempdata set jcrq' + ltrim(rtrim(str(@LOOpi))) + ' = ' + ltrim(rtrim(str(@jcrq))) + ' where id = ' + ltrim(rtrim(str(@id))) select @updateStr -- 开始更新(动态执行更新语句) exec(@updateStr) set @loopi = @loopi + 1 end
CLOSE inGroupcur DEALLOCATE inGroupcur set @loop =@loop + 1 end CLOSE Groupcur DEALLOCATE Groupcur --得到你要的结果 select * from tempData
CREATE TABLE t1 ( F_hm varchar(50), F_lm varchar(20) , F_sj varchar(50) DEFAULT 0 ) set @str='insert into t1 select '+@hm+', CONVERT(varchar(20), '+@lm+',12),'+@sjx+' from '+@bm+'' select @str=@str + ' ' + 'WHERE ('+@lm+' >= ''' + @DateFrom + ''')' select @str=@str + ' ' + 'AND ('+@lm+' <= ''' + @DateTo + ''')' select @str=@str + ' ' + 'order by '+@lm+'' print @str exec (@str) -- set @str='update #t1 set F_lm="F"+F_lm' -- exec (@str) -- select * from #t1 -- 临时表#2 -- 放交叉后的结构 CREATE TABLE t2 ( F_mc varchar(50)) -- F_hj decimal(18,2) null) DECLARE ss_cursor CURSOR FOR SELECT F_lm AS F_lm FROM t1 group by F_lm OPEN ss_cursor FETCH NEXT FROM ss_cursor into @fn while @@fetch_status=0 begin set @str='alter table t2 add F_' + @fn + ' varchar(50) NULL' exec (@str) print @str FETCH NEXT FROM ss_cursor into @fn end close ss_cursor DEALLOCATE ss_cursor INSERT INTO t2 (F_mc) SELECT F_hm FROM t1 GROUP BY f_hm -- select * from #t2
declare @p_lm varchar(50) --列名 declare @sj int --数值数据 declare @v_sj varchar(50) --字符数据 DECLARE ss_cursor CURSOR FOR SELECT F_hm, F_lm, F_sj FROM t1 -- order by F_lm -- GROUP BY F_hm, F_lm,F_sj OPEN ss_cursor FETCH NEXT FROM ss_cursor into @fn,@p_lm,@sj while @@fetch_status=0 begin set @v_sj=convert(varchar(50),@sj) set @str='Update t2 Set F_' + @p_lm + '= ' + @v_sj + ' where f_mc = ''' + @fn + '''' exec (@str) FETCH NEXT FROM ss_cursor into @fn,@p_lm, @sj end close ss_cursor DEALLOCATE ss_cursor select * from t2-- drop table t1 -- drop table t2 GO
insert @t(id,jcrq) select id,jcqq from urTableselect DISTINCT id,jcrq into #jcrq from urTable group by idselect @t.id,@t.jcrq into #jcrq1 from #jcrq,@t
where @t.id=#jcrq.id and @t.seq=(selcet seq from @t where #[email protected])+1select @t.id,@t.jcrq into #jcrq2 from #jcrq1,@t
where @t.id=#jcrq1.id and @t.seq=(selcet seq from @t where #[email protected])+1select @t.id,@t.jcrq into #jcrq3 from #jcrq,@t
where @t.id=#jcrq2.id and @t.seq=(selcet seq from @t where #[email protected])+1select id,jcrq from #jcrq inner join #jcrq1 on #[email protected]
inner join #jcrq2 on #[email protected]
inner join #jcrq3 on #[email protected] ----如果字段数不定,则可用循环的动态SQL语句
比如最大100行id重复,则生成101列(id,jcrq0,jcrq2......jcrq99),你的原始表为urtable(id int ,jcrq int)
你可以根据具体情况来替换列的名字和类型
可能有特殊情况没有考虑周到,我用我的表测试通过,还有注意sql server支持的最大字段数目应该有限制
urtable的结构如下:
CREATE TABLE [dbo].[urtable] (
[id] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[jcrq] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
*/
/**** 变量声明******/
declare @Maxnumber int
declare @loop int
declare @id int
declare @Curr int
declare @groupRows int
declare @jcrq int
declare @updateStr varchar(100)
declare @loopi int
declare @CrearetableStr varchar(500)/** 如果临时表存在删除*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempData]
set @loop =0
set @loopi =0 /**得到可能的最大列,如果有100条id重复的话,则建立jcrq0到jcrq99**/
select @Maxnumber = count(id ) from urtable
group by id
order by count(id )
if @Maxnumber is null
select @Maxnumber = 0/**生成动态Sql语句串,用处为动态建立需要字段数目的表tempData**/
select @CrearetableStr ='create table tempData
(id int '
while (@loop<@Maxnumber )
begin
select @CrearetableStr = @CrearetableStr + ', jcrq'+ ltrim(rtrim(str(@loop))) + ' int'
select @loop =@loop+1
end
select @CrearetableStr = @CrearetableStr + ')'/**动态生成tempData,根据生成的动态sql 语句***/exec(@CrearetableStr)--在tempData表中插入不重复的id,其余字段为空insert tempdata(id) select distinct(id) from urtable----获得分组结果的组数这里使用了@@rowcount全局变量,可能不妥
SELECT id,count(id)
FROM urtable
group by id
select @groupRows = @@rowcountset @loop =0
/**开始更新各行的相应数目的jcrq字段***/
/** 在使用游标时没有根据@@FETCH_STATUS来判断是否取完,因为用到了两个游标,所以无法使用该
全局变量来判断外层用@groupRows,内层根据外层得到的行重复的数目@Curr来判断循环条件*/
DECLARE Groupcur CURSOR FOR
SELECT id,count(id)
FROM urtable
group by id
/**第一个游标类来得到ID重复的情况,即每个ID有几行重复**/OPEN Groupcur
while @loop<@grouprowS
begin
FETCH NEXT FROM Groupcur INTO @id,@Curr
---第二个游标根据给定行的重复情况来决定更新tempData对应行jcrq的个数及内容
DECLARE inGroupcur CURSOR FOR
SELECT jcrq
FROM urtable
where id=@Id
OPEN inGroupcur
set @loopi =0
while @loopi<@curr
begin
FETCH NEXT FROM inGroupcur INTO @jcrq
if @jcrq is null
select @jcrq= 0
--生成更新语句
select @updateStr = 'update tempdata set jcrq' + ltrim(rtrim(str(@LOOpi))) + ' = ' + ltrim(rtrim(str(@jcrq))) + ' where id = ' + ltrim(rtrim(str(@id)))
select @updateStr
-- 开始更新(动态执行更新语句)
exec(@updateStr)
set @loopi = @loopi + 1
end
CLOSE inGroupcur
DEALLOCATE inGroupcur
set @loop =@loop + 1
end
CLOSE Groupcur
DEALLOCATE Groupcur
--得到你要的结果
select * from tempData
jrcqN N是随机的 见:
http://www.csdn.net/expert/topic/774/774792.xml?temp=.5970117
动态列……唉!不多说了!就是你要的!这个程序非常有用呀!呵呵!-- 交叉表查询
-- 处理过程
-- 给出表名,行标头,列标头,数据项,及汇总方式
-- 输出新的汇总表CREATE PROCEDURE share_friend
@DateFrom varchar(20)='', --起始日期
@DateTo varchar (20)='', --结束日期
@bm varchar(50)='', --表名
@hm varchar(50)='', --行标头
@lm varchar(20), --列标头
@sjx varchar(50) --数据项 --@fs int --方式 1、sum 2、count 3、avg 4、max 5、min AS
declare @str varchar(350) --字符串
declare @fn varchar(50)
-- 临时表#t1
-- 根据参数项,通过表名,把行数据和列数据读到#t1中
-- 考虑到用户有可能列标头出现数值所以系统自动为列标头加“f_”
CREATE TABLE t1 (
F_hm varchar(50),
F_lm varchar(20) ,
F_sj varchar(50) DEFAULT 0 )
set @str='insert into t1 select '+@hm+', CONVERT(varchar(20), '+@lm+',12),'+@sjx+' from '+@bm+''
select @str=@str + ' ' + 'WHERE ('+@lm+' >= ''' + @DateFrom + ''')'
select @str=@str + ' ' + 'AND ('+@lm+' <= ''' + @DateTo + ''')'
select @str=@str + ' ' + 'order by '+@lm+''
print @str
exec (@str)
-- set @str='update #t1 set F_lm="F"+F_lm'
-- exec (@str)
-- select * from #t1
-- 临时表#2
-- 放交叉后的结构 CREATE TABLE t2 (
F_mc varchar(50))
-- F_hj decimal(18,2) null) DECLARE ss_cursor CURSOR FOR SELECT F_lm AS F_lm FROM t1 group by F_lm OPEN ss_cursor FETCH NEXT FROM ss_cursor
into @fn
while @@fetch_status=0
begin set @str='alter table t2 add F_' + @fn + ' varchar(50) NULL'
exec (@str)
print @str
FETCH NEXT FROM ss_cursor
into @fn
end close ss_cursor DEALLOCATE ss_cursor
INSERT INTO t2 (F_mc) SELECT F_hm FROM t1 GROUP BY f_hm -- select * from #t2
declare @p_lm varchar(50) --列名
declare @sj int --数值数据
declare @v_sj varchar(50) --字符数据 DECLARE ss_cursor CURSOR FOR
SELECT F_hm, F_lm, F_sj
FROM t1
-- order by F_lm
-- GROUP BY F_hm, F_lm,F_sj OPEN ss_cursor FETCH NEXT FROM ss_cursor
into @fn,@p_lm,@sj
while @@fetch_status=0
begin
set @v_sj=convert(varchar(50),@sj)
set @str='Update t2 Set F_' + @p_lm + '= ' + @v_sj + ' where f_mc = ''' + @fn + ''''
exec (@str)
FETCH NEXT FROM ss_cursor
into @fn,@p_lm, @sj end
close ss_cursor
DEALLOCATE ss_cursor select * from t2-- drop table t1
-- drop table t2
GO