create table 表一(sGroundNo char(5),iIndex int,iFactorClassSn int)
insert 表一 select 'zd001', 100, 1
union all select 'zd001', 100 , 2
union all select 'zd001', 99 , 3
union all select 'zd001', 100 , 4
union all select 'zd001', 100 , 5
union all select 'zd001', 100 , 6
union all select 'zd001', 100 , 9
union all select 'zd001', 100 , 12
union all select 'zd001', 100 , 16
union all select 'zd001', 100, 14
union all select 'zd001', 100 , 15
union all select 'zd001', 100 , 17create table 表二(sGroundNo char(5), sExampleNo int, iIndex numeric(10,3), iFactorClassSn int)
insert 表二 select 'zd001', 12, 102.000, 1
union all select 'zd001', 12 , 100.000 ,2
union all select 'zd001', 12 , 100.000 , 3
union all select 'zd001', 12 , 100.000 , 4
union all select 'zd001', 12 , 97.475 , 5
union all select 'zd001', 12 , 100.000 , 6
union all select 'zd001', 12 , 100.000 , 9
union all select 'zd001', 12 , 100.000, 12
union all select 'zd001', 12 , 100.000 ,16
union all select 'zd001', 12 , 100.000 , 14
union all select 'zd001', 12 , 100.000 , 15
union all select 'zd001', 12 , NULL , 17
union all select 'zd001', 13 , 100.000 , 1
union all select 'zd001', 13 , 100.000 , 2
union all select 'zd001', 13 , 100.000, 3
union all select 'zd001', 13 , 100.000 , 4
union all select 'zd001', 13 , 98.978 , 5
union all select 'zd001', 13 , 100.000 , 6
union all select 'zd001', 13 , 100.000 , 9
union all select 'zd001', 13 ,100.000 , 12
union all select 'zd001', 13, 100.000, 16
union all select 'zd001', 13 , 100.000 ,14
union all select 'zd001', 13 , 100.000 , 15
union all select 'zd001', 13 , NULL , 17
union all select 'zd001', 14 , 100.000 , 1
union all select 'zd001', 14 , 100.000 , 2
union all select 'zd001', 14 , 100.000 , 3
union all select 'zd001', 14 , 100.000, 4
union all select 'zd001', 14 , 99.215 ,5
union all select 'zd001', 14 , 100.000 , 6
union all select 'zd001', 14 , 100.000 , 9
union all select 'zd001', 14 , 100.000 , 12
union all select 'zd001', 14 , 100.000 , 16
union all select 'zd001', 14 , 100.000 , 14
union all select 'zd001', 14 , 100.000, 15
union all select 'zd001', 14 , NULL , 17declare @sql varchar(8000)
set @sql = 'select 表二.iFactorClassSn,表一.iIndex'select @sql = @sql + ',cast(表一.iIndex as varchar(10))+''/''+cast(sum(case sExampleNo when '''+ cast(sExampleNo as varchar(10)) +''' then isnull(表二.iIndex,''0'') else ''0'' end) as varchar(10)) ['+ cast(sExampleNo as varchar(10))+']'
from (select distinct sExampleNo from 表二) as aexec(@sql+' from 表二,表一 where 表二.iFactorClassSn = 表一.iFactorClassSn group by 表二.iFactorClassSn,表一.iIndex order by 表二.iFactorClassSn')/*
iFactorClassSn iIndex 12 13 14
-------------- ----------- --------------------- --------------------- ---------------------
1 100 100/102.000 100/100.000 100/100.000
2 100 100/100.000 100/100.000 100/100.000
3 99 99/100.000 99/100.000 99/100.000
4 100 100/100.000 100/100.000 100/100.000
5 100 100/97.475 100/98.978 100/99.215
6 100 100/100.000 100/100.000 100/100.000
9 100 100/100.000 100/100.000 100/100.000
12 100 100/100.000 100/100.000 100/100.000
14 100 100/100.000 100/100.000 100/100.000
15 100 100/100.000 100/100.000 100/100.000
16 100 100/100.000 100/100.000 100/100.000
17 100 100/0.000 100/0.000 100/0.000
*/
insert 表一 select 'zd001', 100, 1
union all select 'zd001', 100 , 2
union all select 'zd001', 99 , 3
union all select 'zd001', 100 , 4
union all select 'zd001', 100 , 5
union all select 'zd001', 100 , 6
union all select 'zd001', 100 , 9
union all select 'zd001', 100 , 12
union all select 'zd001', 100 , 16
union all select 'zd001', 100, 14
union all select 'zd001', 100 , 15
union all select 'zd001', 100 , 17create table 表二(sGroundNo char(5), sExampleNo int, iIndex numeric(10,3), iFactorClassSn int)
insert 表二 select 'zd001', 12, 102.000, 1
union all select 'zd001', 12 , 100.000 ,2
union all select 'zd001', 12 , 100.000 , 3
union all select 'zd001', 12 , 100.000 , 4
union all select 'zd001', 12 , 97.475 , 5
union all select 'zd001', 12 , 100.000 , 6
union all select 'zd001', 12 , 100.000 , 9
union all select 'zd001', 12 , 100.000, 12
union all select 'zd001', 12 , 100.000 ,16
union all select 'zd001', 12 , 100.000 , 14
union all select 'zd001', 12 , 100.000 , 15
union all select 'zd001', 12 , NULL , 17
union all select 'zd001', 13 , 100.000 , 1
union all select 'zd001', 13 , 100.000 , 2
union all select 'zd001', 13 , 100.000, 3
union all select 'zd001', 13 , 100.000 , 4
union all select 'zd001', 13 , 98.978 , 5
union all select 'zd001', 13 , 100.000 , 6
union all select 'zd001', 13 , 100.000 , 9
union all select 'zd001', 13 ,100.000 , 12
union all select 'zd001', 13, 100.000, 16
union all select 'zd001', 13 , 100.000 ,14
union all select 'zd001', 13 , 100.000 , 15
union all select 'zd001', 13 , NULL , 17
union all select 'zd001', 14 , 100.000 , 1
union all select 'zd001', 14 , 100.000 , 2
union all select 'zd001', 14 , 100.000 , 3
union all select 'zd001', 14 , 100.000, 4
union all select 'zd001', 14 , 99.215 ,5
union all select 'zd001', 14 , 100.000 , 6
union all select 'zd001', 14 , 100.000 , 9
union all select 'zd001', 14 , 100.000 , 12
union all select 'zd001', 14 , 100.000 , 16
union all select 'zd001', 14 , 100.000 , 14
union all select 'zd001', 14 , 100.000, 15
union all select 'zd001', 14 , NULL , 17declare @sql varchar(8000)
set @sql = 'select 表二.iFactorClassSn,表一.iIndex'select @sql = @sql + ',cast(表一.iIndex as varchar(10))+''/''+cast(sum(case sExampleNo when '''+ cast(sExampleNo as varchar(10)) +''' then isnull(表二.iIndex,''0'') else ''0'' end) as varchar(10)) ['+ cast(sExampleNo as varchar(10))+']'
from (select distinct sExampleNo from 表二) as aexec(@sql+' from 表二,表一 where 表二.iFactorClassSn = 表一.iFactorClassSn group by 表二.iFactorClassSn,表一.iIndex order by 表二.iFactorClassSn')/*
iFactorClassSn iIndex 12 13 14
-------------- ----------- --------------------- --------------------- ---------------------
1 100 100/102.000 100/100.000 100/100.000
2 100 100/100.000 100/100.000 100/100.000
3 99 99/100.000 99/100.000 99/100.000
4 100 100/100.000 100/100.000 100/100.000
5 100 100/97.475 100/98.978 100/99.215
6 100 100/100.000 100/100.000 100/100.000
9 100 100/100.000 100/100.000 100/100.000
12 100 100/100.000 100/100.000 100/100.000
14 100 100/100.000 100/100.000 100/100.000
15 100 100/100.000 100/100.000 100/100.000
16 100 100/100.000 100/100.000 100/100.000
17 100 100/0.000 100/0.000 100/0.000
*/
select a.单位 as 单位,a.描述 as 分类1,b.描述 as 分类2,c.描述 as 分类3
from a.yourtable,b.yourtable,c.yourtable
where a.分类=分类1 and b.分类=分类2 and c.分类=分类3
表a:
单位 分类 描述
单位1 分类1 aaaa
单位2 分类1 dddd
表b:
单位 分类 描述
单位1 分类2 bbbb
单位2 分类2 eeee
表c:
单位 分类 描述
单位1 分类3 cccc
单位2 分类3 ffff再联和查询
就用:
select 单位,
max(case when 分类=分类1 then 描述 else '' end) 分类1,
max(case when 分类=分类2 then 描述 else '' end) 分类2,
max(case when 分类=分类3 then 描述 else '' end) 分类3
from t
group by 单位
select 单位
,分类1=max(case 分类 when '分类1' then 描述 end)
,分类2=max(case 分类 when '分类2' then 描述 end)
,分类3=max(case 分类 when '分类3' then 描述 end)
from 表 group by 单位
go--如果分类不固定的处理方法
declare @s varchar(8000)
set @s=''
select @s=@s+',['+分类+']=max(case 分类 when '''+分类+''' then 描述 end)'
from(select distinct 分类 from 表)a
exec('select 单位'+@s+'from 表 group by 单位')
go
create table 表(单位 char(5),分类 char(5),描述 char(4))
insert into 表
select '单位1','分类1','aaaa'
union all select '单位1','分类2','bbbb'
union all select '单位1','分类3','cccc'
union all select '单位2','分类1','dddd'
union all select '单位2','分类2','eeee'
union all select '单位2','分类3','ffff'
go--如果分类固定的处理方法
select 单位
,分类1=max(case 分类 when '分类1' then 描述 end)
,分类2=max(case 分类 when '分类2' then 描述 end)
,分类3=max(case 分类 when '分类3' then 描述 end)
from 表 group by 单位
go--如果分类不固定的处理方法
declare @s varchar(8000)
set @s=''
select @s=@s+',['+分类+']=max(case 分类 when '''+分类+''' then 描述 end)'
from(select distinct 分类 from 表)a
exec('select 单位'+@s+'from 表 group by 单位')
go--删除测试环境
drop table 表
go/*--测试结果--如果分类固定的处理方法的测试结果单位 分类1 分类2 分类3
----- ---- ---- ----
单位1 aaaa bbbb cccc
单位2 dddd eeee ffff(所影响的行数为 2 行)--如果分类不固定的处理方法的测试结果
单位 分类1 分类2 分类3
----- ---- ---- ----
单位1 aaaa bbbb cccc
单位2 dddd eeee ffff--*/
,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
,@i int,@ic varchar(20)--生成数据处理临时表
select id=identity(int,0,1),gid=0
,a=',['+分类+']=max(case 分类 when '''+分类+''' then 描述 end)'
into # from(select distinct 分类 from 表) a--判断需要多少个变量来处理
select @i=max(len(a)) from #
set @i=3800/@i--分组临时表
update # set gid=id/@i
select @i=max(gid) from #--生成数据处理语句
select @sqlhead='''select 单位'''
,@sqlend=''' from 表 group by 单位'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
,@sql1='@'+@ic+' nvarchar(4000),'+@sql1
,@sql2=@sql2+'@'+@ic+'='''','
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic
+char(13)+@sql3
,@sql4=@sql4+',@'+@icselect @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
,@sql2=left(@sql2,len(@sql2)-1)+char(13)
,@sql3=left(@sql3,len(@sql3)-1)
,@sql4=substring(@sql4,2,4000)--执行
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
)--删除临时表
drop table #
go