select *,a=cast('' as varchar(8000)) into #t from posmachiningforprinting_pmp declare @a char(12),@c varchar(8000) update #t set @c=case @a when pmp_cSheetID then @a+','+pmp_cProcessPosition+':'+pmp_cProcessCode else pmp_cSheetID+':'+pmp_cProcessPosition+':'+pmp_cProcessCode end a=@c,@a=pmp_cSheetIDselect pmp_cSheetID,max(a) from #t group by pmp_cSheetIDdrop table #t
多一個欄位,不知怎樣修改!Transact-SQL語言不熟練!
前天我發的帖子中(求交叉表),數據類型為varchar型可以實現,而char型不可以實現,為什麼?
--上面的有些问题,因为以前没有完全理解楼主的意思,改用下面的:select *,a=cast('' as varchar(8000)) into #t from posmachiningforprinting_pmp order by pmp_cSheetID,pmp_cProcessPosition desc,pmp_cProcessCodedeclare @a varchar(12),@b varchar(1),@c varchar(8000) update #t set @c=case @b when pmp_cProcessPosition then @c+','+rtrim(pmp_cProcessCode) else case @a when pmp_cSheetID then @c+':'+rtrim(pmp_cProcessPosition)+':'+rtrim(pmp_cProcessCode) else rtrim(pmp_cSheetID)+':'+rtrim(pmp_cProcessPosition)+':'+rtrim(pmp_cProcessCode) end end ,a=@c,@a=pmp_cSheetID,@b=pmp_cProcessPositionselect pmp_cSheetID,max(a) from #t group by pmp_cSheetIDdrop table #t
--数据测试表 declare @posmachiningforprinting_pmp table(pmp_cSheetID Char(12), pmp_cProcessPosition Char(1),pmp_cProcessCode Char(6))insert into @posmachiningforprinting_pmp select '1','F','GY' union all select '1','F','JBU' union all select '1','F','UV' union all select '1','Z','GY' union all select '1','Z','UV' union all select '2','Z','GY' union all select '2','Z','JBU' union all select '2','Z','UV' --数据处理 select *,a=cast('' as varchar(8000)) into #t from @posmachiningforprinting_pmp order by pmp_cSheetID,pmp_cProcessPosition desc,pmp_cProcessCodedeclare @a varchar(12),@b varchar(1),@c varchar(8000) update #t set @c=case @b when pmp_cProcessPosition then @c+','+rtrim(pmp_cProcessCode) else case @a when pmp_cSheetID then @c+':'+rtrim(pmp_cProcessPosition)+':'+rtrim(pmp_cProcessCode) else rtrim(pmp_cSheetID)+':'+rtrim(pmp_cProcessPosition)+':'+rtrim(pmp_cProcessCode) end end ,a=@c,@a=pmp_cSheetID,@b=pmp_cProcessPosition--显示处理结果 select pmp_cSheetID,max(a) from #t group by pmp_cSheetID--删除临时表 drop table #t/*--测试结果: pmp_cSheetID ------------ ------------------------ 1 1:Z:GY,UV:F:GY,JBU,UV 2 2:Z:GY,JBU,UV(所影响的行数为 2 行) --*/
测试:(不用自定义函数) drop table t1 go create table t1(column1 char(12),column2 char(1),column3 char(6)) insert t1 select '1','F','GY' union all select '1','F','JBU' union all select '1','F','UV' union all select '1','Z','GY' union all select '1','Z','UV' union all select '2','Z','GY' union all select '2','Z','JBU' union all select '2','Z','UV' select *,cast('' as varchar(8000)) value into #t from t1 declare @a varchar(8000),@b char(6),@c char(1) update #t set @a=case when @b=rtrim(column1) and @c = column2 then @a+',' else '' end+rtrim(column3), @b=rtrim(column1),@c = column2,value=@aselect column1,column2,max(value) result,space(8000) as value into #t2 from #t group by column1,column2update #t2 set @a=case when @b=rtrim(column1) then @a+',' else '' end+column2+':'+rtrim(result), @b=rtrim(column1),value=@aSelect result from (select column1,max(rtrim(column1)+':'+value) as result from #t2 group by column1) aaresult ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1:F:GY,JBU,UV,Z:GY,UV 2:Z:GY,JBU,UV(所影响的行数为 2 行)
分两步: 第一步用一个临时表生成 1 F GY,JBU,UV 1 Z GY,UV 2 Z GY,JBU,UV 第二步用同样办法生成结果。
http://expert.csdn.net/Expert/topic/2479/2479536.xml?temp=.1746485
declare @a char(12),@c varchar(8000)
update #t set @c=case @a when pmp_cSheetID
then @a+','+pmp_cProcessPosition+':'+pmp_cProcessCode
else pmp_cSheetID+':'+pmp_cProcessPosition+':'+pmp_cProcessCode
end
a=@c,@a=pmp_cSheetIDselect pmp_cSheetID,max(a) from #t group by pmp_cSheetIDdrop table #t
order by pmp_cSheetID,pmp_cProcessPosition desc,pmp_cProcessCodedeclare @a varchar(12),@b varchar(1),@c varchar(8000)
update #t set @c=case @b when pmp_cProcessPosition
then @c+','+rtrim(pmp_cProcessCode)
else case @a when pmp_cSheetID
then @c+':'+rtrim(pmp_cProcessPosition)+':'+rtrim(pmp_cProcessCode)
else rtrim(pmp_cSheetID)+':'+rtrim(pmp_cProcessPosition)+':'+rtrim(pmp_cProcessCode)
end
end
,a=@c,@a=pmp_cSheetID,@b=pmp_cProcessPositionselect pmp_cSheetID,max(a) from #t group by pmp_cSheetIDdrop table #t
declare @posmachiningforprinting_pmp table(pmp_cSheetID Char(12),
pmp_cProcessPosition Char(1),pmp_cProcessCode Char(6))insert into @posmachiningforprinting_pmp
select '1','F','GY'
union all select '1','F','JBU'
union all select '1','F','UV'
union all select '1','Z','GY'
union all select '1','Z','UV'
union all select '2','Z','GY'
union all select '2','Z','JBU'
union all select '2','Z','UV' --数据处理
select *,a=cast('' as varchar(8000)) into #t from @posmachiningforprinting_pmp
order by pmp_cSheetID,pmp_cProcessPosition desc,pmp_cProcessCodedeclare @a varchar(12),@b varchar(1),@c varchar(8000)
update #t set @c=case @b when pmp_cProcessPosition
then @c+','+rtrim(pmp_cProcessCode)
else case @a when pmp_cSheetID
then @c+':'+rtrim(pmp_cProcessPosition)+':'+rtrim(pmp_cProcessCode)
else rtrim(pmp_cSheetID)+':'+rtrim(pmp_cProcessPosition)+':'+rtrim(pmp_cProcessCode)
end
end
,a=@c,@a=pmp_cSheetID,@b=pmp_cProcessPosition--显示处理结果
select pmp_cSheetID,max(a) from #t group by pmp_cSheetID--删除临时表
drop table #t/*--测试结果:
pmp_cSheetID
------------ ------------------------
1 1:Z:GY,UV:F:GY,JBU,UV
2 2:Z:GY,JBU,UV(所影响的行数为 2 行)
--*/
drop table t1
go
create table t1(column1 char(12),column2 char(1),column3 char(6))
insert t1 select '1','F','GY'
union all select '1','F','JBU'
union all select '1','F','UV'
union all select '1','Z','GY'
union all select '1','Z','UV'
union all select '2','Z','GY'
union all select '2','Z','JBU'
union all select '2','Z','UV' select *,cast('' as varchar(8000)) value into #t from t1
declare @a varchar(8000),@b char(6),@c char(1)
update #t set @a=case when @b=rtrim(column1) and @c = column2 then @a+',' else '' end+rtrim(column3),
@b=rtrim(column1),@c = column2,value=@aselect column1,column2,max(value) result,space(8000) as value into #t2 from #t group by column1,column2update #t2 set @a=case when @b=rtrim(column1) then @a+',' else '' end+column2+':'+rtrim(result),
@b=rtrim(column1),value=@aSelect result from (select column1,max(rtrim(column1)+':'+value) as result from #t2 group by column1) aaresult
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1:F:GY,JBU,UV,Z:GY,UV
2:Z:GY,JBU,UV(所影响的行数为 2 行)
第一步用一个临时表生成
1 F GY,JBU,UV
1 Z GY,UV
2 Z GY,JBU,UV
第二步用同样办法生成结果。
不說太多啦!散分!