22,210
社区成员
发帖
与我相关
我的任务
分享
if object_id('[taborder]') is not null drop table [taborder]
go
create table [taborder]([预定号] int,[WBS] varchar(2))
insert [taborder]
select 1001,'E1' union all
select 1001,'E2' union all
select 1002,'E1'
GO
if object_id('[tabpart]') is not null drop table [tabpart]
go
create table [tabpart]([预定号] int,[WBS] varchar(2),[类别] varchar(6),[型号] varchar(2),[物号] int,[数量] int)
insert [tabpart]
select 1001,'E1','class1','A1',2000,1 union all
select 1001,'E1','class2','B1',2001,100 union all
select 1001,'E1','class3','C1',2002,1 union all
select 1001,'E2','class1','A1',2000,1 union all
select 1001,'E2','class2','B1',2001,100
GO
if object_id('[tabclass]') is not null drop table [tabclass]
go
create table [tabclass]([类别] varchar(6))
insert [tabclass]
select 'class1' union all
select 'class2' union all
select 'class3' union all
select 'class4'
GO
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'max(case when 类别='''+类别+''' then ''TRUE'' else ''FALSE'' end) as ['+类别+'],'
+'max(case when 类别='''+类别+''' then 型号 end) as ['+类别+'型号],'
+'max(case when 类别='''+类别+''' then 物号 end) as ['+类别+'物号],'
+'sum(case when 类别='''+类别+''' then 数量 end) as ['+类别+'数量]'
from tabclass
set @sql='select 预定号,WBS,'+@sql+' from tabpart group by 预定号,WBS'
exec (@sql)
/**
预定号 WBS class1 class1型号 class1物号 class1数量 class2 class2型号 class2物号 class2数量 class3 class3型号 class3物号 class3数量 class4 class4型号 class4物号 class4数量
----------- ---- ------ -------- ----------- ----------- ------ -------- ----------- ----------- ------ -------- ----------- ----------- ------ -------- ----------- -----------
1001 E1 TRUE A1 2000 1 TRUE B1 2001 100 TRUE C1 2002 1 FALSE NULL NULL NULL
1001 E2 TRUE A1 2000 1 TRUE B1 2001 100 FALSE NULL NULL NULL FALSE NULL NULL NULL
(2 行受影响)
**/
select @sql=isnull(@sql+',','')
+ 'max(case when partCatelog='''+ cast( catelogId as nvarchar)+''' then ''True'' else ''FALSE'' end) as ['+catelogName+'],'