--示例--示例数据 create table FS_VATCustomerCode(id int,VATCustomerCode varchar(20)) insert FS_VATCustomerCode select 1,'John' union all select 2,'Mike' union all select 3,'Jully'create table FS_VATProductCode(id int,VATProductCode varchar(20)) insert FS_VATProductCode select 1,'Bike' union all select 2,'Car' union all select 3,'Tractor'create table FS_VATProductCustomerCombination(VATCustomerCode varchar(20),VATProductCode varchar(20),Tax varchar(20)) insert FS_VATProductCustomerCombination select 'John' ,'Bike' ,'2' union all select 'John' ,'Car' ,' 500' union all select 'Mike' ,'Tractor','149' union all select 'Jully','Bike' ,'2' union all select 'Jully','Car' ,'388' union all select 'Jully','Tractor','149' go--查询 declare @s2 varchar(8000),@s3 varchar(8000) select @s2='',@s3='' select @s2=@s2+'+space(6)+cast('''+VATCustomerCode+''' as char(10))' ,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when ''' +VATCustomerCode+''' then Tax else '''' end) as char(10))' from FS_VATCustomerCode order by id select @s2=stuff(@s2,1,10,''),@s3=stuff(@s3,1,10,'') exec(' declare @tb table(ColumnA varchar(20),ColumnB varchar(8000)) insert @tb select ''ProductCode'','+@s2+' insert @tb select b.VATProductCode,'+@s3+' from FS_VATProductCode a,FS_VATProductCustomerCombination b where a.VATProductCode=b.VATProductCode group by b.VATProductCode,a.id order by a.id select * from @tb') godrop table FS_VATCustomerCode,FS_VATProductCode,FS_VATProductCustomerCombination/*--测试结果ColumnA ColumnB -------------------- ----------------------------------------- ProductCode John Mike Jully Bike 2 2 Car 500 388 Tractor 149 149 --*/
搜一下大把的例子 case
我想把Query出来的结果集中的空白,替换为"*"该如何考虑呢?
--示例--示例数据 create table FS_VATCustomerCode(id int,VATCustomerCode varchar(20)) insert FS_VATCustomerCode select 1,'John' union all select 2,'Mike' union all select 3,'Jully'create table FS_VATProductCode(id int,VATProductCode varchar(20)) insert FS_VATProductCode select 1,'Bike' union all select 2,'Car' union all select 3,'Tractor'create table FS_VATProductCustomerCombination(VATCustomerCode varchar(20),VATProductCode varchar(20),Tax varchar(20)) insert FS_VATProductCustomerCombination select 'John' ,'Bike' ,'2' union all select 'John' ,'Car' ,' 500' union all select 'Mike' ,'Tractor','149' union all select 'Jully','Bike' ,'2' union all select 'Jully','Car' ,'388' union all select 'Jully','Tractor','149' go--查询 declare @s2 varchar(8000),@s3 varchar(8000) select @s2='',@s3='' select @s2=@s2+'+space(6)+cast('''+VATCustomerCode+''' as char(10))' ,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when ''' +VATCustomerCode+''' then Tax else ''*'' end) as char(10))' from FS_VATCustomerCode order by id select @s2=stuff(@s2,1,10,''),@s3=stuff(@s3,1,10,'') exec(' declare @tb table(ColumnA varchar(20),ColumnB varchar(8000)) insert @tb select ''ProductCode'','+@s2+' insert @tb select b.VATProductCode,'+@s3+' from FS_VATProductCode a,FS_VATProductCustomerCombination b where a.VATProductCode=b.VATProductCode group by b.VATProductCode,a.id order by a.id select * from @tb') godrop table FS_VATCustomerCode,FS_VATProductCode,FS_VATProductCustomerCombination/*--测试结果ColumnA ColumnB -------------------- ----------------------------------------- ProductCode John Mike Jully Bike 2 * 2 Car * * 388 Tractor * 149 149 --*/
数据类型问题 邹键的VATCustomerCode是varchar的,你的是char的,你这么改:select @s2=@s2+'+space(6)+cast('''+VATCustomerCode+''' as char(10))' ,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when ''' +VATCustomerCode+''' then Tax else ''*'' end) as char(10))'--〉 select @s2=@s2+'+space(6)+cast('''+rtrim(VATCustomerCode)+''' as char(10))' ,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when ''' +rtrim(VATCustomerCode)+''' then Tax else ''*'' end) as char(10))'呵呵
declare @s2 varchar(8000),@s3 varchar(8000) select @s2='',@s3='' select @s2=@s2+'+space(6)+cast('''+VATCustomerCode+''' as char(10))' ,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when ''' +VATCustomerCode+''' then PrimaryTaxCode else ''FD'' end) as char(10))' from FS_VATCustomerCode order by VATCustomerCodeKey select @s2=stuff(@s2,1,10,''),@s3=stuff(@s3,1,10,'') exec(' declare @tb table(ColumnA varchar(20),ColumnB varchar(8000)) insert @tb select ''ProductCode'','+@s2+' insert @tb select b.VATProductCode,'+@s3+' from FS_VATProductCode a,FS_VATProductCustomerCombination b where a.VATProductCode=b.VATProductCode group by b.VATProductCode,a.VATProductCodeKey order by a.VATProductCodeKey select * from @tb')有一点小出入 第五行 PrimaryTaxCode --Tax 第六行 VATCustomerCodeKey - id倒数第二行 a.VATProductCodeKey --a.id 最末行 a.VATProductCodeKey --a.id
CREATE TABLE [FS_VATCustomerCode] ( [VATCustomerCode] [char] (5) COLLATE Latin1_General_BIN NOT NULL , [VATCustomerCodeKey] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [FS_VATProductCode] ( [VATProductCode] [varchar] (12) COLLATE Latin1_General_BIN NOT NULL , [VATProductCodeKey] [int] NOT NULL ) ON [PRIMARY] GOCREATE TABLE [FS_VATProductCustomerCombination ] ( [PrimaryTaxCode] [char] (2) COLLATE Latin1_General_BIN NOT NULL , [VATCustomerCode] [char] (5) COLLATE Latin1_General_BIN NOT NULL , [VATProductCode] [varchar] (12) COLLATE Latin1_General_BIN NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [FS_VATCustomerCode] ( [VATCustomerCode] [char] (5) COLLATE Latin1_General_BIN NOT NULL , [VATCustomerCodeKey] [int] NOT NULL ) ON [PRIMARY] insert FS_VATCustomerCode select 'John' ,1 union all select 'Mike' ,2 union all select 'Jully',3 GO CREATE TABLE [FS_VATProductCode] ( [VATProductCode] [varchar] (12) COLLATE Latin1_General_BIN NOT NULL , [VATProductCodeKey] [int] NOT NULL ) ON [PRIMARY] insert FS_VATProductCode select 'Bike' ,1 union all select 'Car' ,2 union all select 'Tractor',3 GO CREATE TABLE [FS_VATProductCustomerCombination ] ( [VATCustomerCode] [char] (5) COLLATE Latin1_General_BIN NOT NULL , [VATProductCode] [varchar] (12) COLLATE Latin1_General_BIN NOT NULL , [PrimaryTaxCode] [char] (2) COLLATE Latin1_General_BIN NOT NULL ) ON [PRIMARY] insert FS_VATProductCustomerCombination select 'John' ,'Bike' ,'2' union all select 'John' ,'Car' ,'3' union all select 'Mike' ,'Tractor','4' union all select 'Jully','Bike' ,'5' union all select 'Jully','Car' ,'6' union all select 'Jully','Tractor','7' GOdeclare @s2 varchar(8000),@s3 varchar(8000) select @s2='',@s3='' select @s2=@s2+'+cast('''' as char(6))+cast('''+rtrim(VATCustomerCode)+''' as char(10))' ,@s3=@s3+'+cast('''' as char(6))+cast(isnull(max(case b.VATCustomerCode when ''' +rtrim(VATCustomerCode)+''' then PrimaryTaxCode end),''FD'') as char(10))' from FS_VATCustomerCode order by VATCustomerCodeKey select @s2=stuff(@s2,1,21,''),@s3=stuff(@s3,1,21,'') exec(' declare @tb table(ColumnA varchar(20),ColumnB varchar(8000)) insert @tb select ''ProductCode'','+@s2+' insert @tb select b.VATProductCode,'+@s3+' from FS_VATProductCode a,FS_VATProductCustomerCombination b where a.VATProductCode=b.VATProductCode group by b.VATProductCode,a.VATProductCodeKey order by a.VATProductCodeKey select * from @tb') go drop table FS_VATCustomerCode,FS_VATProductCode,FS_VATProductCustomerCombination/*--测试结果ColumnA ColumnB -------------------- ----------------------------------------- ProductCode John Mike Jully Bike 2 FD 5 Car 3 FD 6 Tractor FD 4 7 --*/
create table FS_VATCustomerCode(id int,VATCustomerCode varchar(20))
insert FS_VATCustomerCode select 1,'John'
union all select 2,'Mike'
union all select 3,'Jully'create table FS_VATProductCode(id int,VATProductCode varchar(20))
insert FS_VATProductCode select 1,'Bike'
union all select 2,'Car'
union all select 3,'Tractor'create table FS_VATProductCustomerCombination(VATCustomerCode varchar(20),VATProductCode varchar(20),Tax varchar(20))
insert FS_VATProductCustomerCombination select 'John' ,'Bike' ,'2'
union all select 'John' ,'Car' ,' 500'
union all select 'Mike' ,'Tractor','149'
union all select 'Jully','Bike' ,'2'
union all select 'Jully','Car' ,'388'
union all select 'Jully','Tractor','149'
go--查询
declare @s2 varchar(8000),@s3 varchar(8000)
select @s2='',@s3=''
select @s2=@s2+'+space(6)+cast('''+VATCustomerCode+''' as char(10))'
,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when '''
+VATCustomerCode+''' then Tax else '''' end) as char(10))'
from FS_VATCustomerCode order by id
select @s2=stuff(@s2,1,10,''),@s3=stuff(@s3,1,10,'')
exec('
declare @tb table(ColumnA varchar(20),ColumnB varchar(8000))
insert @tb
select ''ProductCode'','+@s2+'
insert @tb
select b.VATProductCode,'+@s3+'
from FS_VATProductCode a,FS_VATProductCustomerCombination b
where a.VATProductCode=b.VATProductCode
group by b.VATProductCode,a.id
order by a.id
select * from @tb')
godrop table FS_VATCustomerCode,FS_VATProductCode,FS_VATProductCustomerCombination/*--测试结果ColumnA ColumnB
-------------------- -----------------------------------------
ProductCode John Mike Jully
Bike 2 2
Car 500 388
Tractor 149 149
--*/
create table FS_VATCustomerCode(id int,VATCustomerCode varchar(20))
insert FS_VATCustomerCode select 1,'John'
union all select 2,'Mike'
union all select 3,'Jully'create table FS_VATProductCode(id int,VATProductCode varchar(20))
insert FS_VATProductCode select 1,'Bike'
union all select 2,'Car'
union all select 3,'Tractor'create table FS_VATProductCustomerCombination(VATCustomerCode varchar(20),VATProductCode varchar(20),Tax varchar(20))
insert FS_VATProductCustomerCombination select 'John' ,'Bike' ,'2'
union all select 'John' ,'Car' ,' 500'
union all select 'Mike' ,'Tractor','149'
union all select 'Jully','Bike' ,'2'
union all select 'Jully','Car' ,'388'
union all select 'Jully','Tractor','149'
go--查询
declare @s2 varchar(8000),@s3 varchar(8000)
select @s2='',@s3=''
select @s2=@s2+'+space(6)+cast('''+VATCustomerCode+''' as char(10))'
,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when '''
+VATCustomerCode+''' then Tax else ''*'' end) as char(10))'
from FS_VATCustomerCode order by id
select @s2=stuff(@s2,1,10,''),@s3=stuff(@s3,1,10,'')
exec('
declare @tb table(ColumnA varchar(20),ColumnB varchar(8000))
insert @tb
select ''ProductCode'','+@s2+'
insert @tb
select b.VATProductCode,'+@s3+'
from FS_VATProductCode a,FS_VATProductCustomerCombination b
where a.VATProductCode=b.VATProductCode
group by b.VATProductCode,a.id
order by a.id
select * from @tb')
godrop table FS_VATCustomerCode,FS_VATProductCode,FS_VATProductCustomerCombination/*--测试结果ColumnA ColumnB
-------------------- -----------------------------------------
ProductCode John Mike Jully
Bike 2 * 2
Car * * 388
Tractor * 149 149
--*/
ColumnA ColumnB
-------------------- -----------------------------------------
ProductCode * * *
Bike * * *
Car * * *
Tractor * * *
但是实际情况是我需要替代为'FD' 结果就变成ColumnA ColumnB
-------------------- -----------------------------------------
ProductCode FD FD FD
Bike FD FD FD
Car FD FD FD
Tractor FD FD FD 的错误结果了...
为什么呢?
邹键的VATCustomerCode是varchar的,你的是char的,你这么改:select @s2=@s2+'+space(6)+cast('''+VATCustomerCode+''' as char(10))'
,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when '''
+VATCustomerCode+''' then Tax else ''*'' end) as char(10))'--〉
select @s2=@s2+'+space(6)+cast('''+rtrim(VATCustomerCode)+''' as char(10))'
,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when '''
+rtrim(VATCustomerCode)+''' then Tax else ''*'' end) as char(10))'呵呵
select @s2='',@s3=''
select @s2=@s2+'+space(6)+cast('''+VATCustomerCode+''' as char(10))'
,@s3=@s3+'+space(6)+cast(max(case b.VATCustomerCode when '''
+VATCustomerCode+''' then PrimaryTaxCode else ''FD'' end) as char(10))'
from FS_VATCustomerCode order by VATCustomerCodeKey
select @s2=stuff(@s2,1,10,''),@s3=stuff(@s3,1,10,'')
exec('
declare @tb table(ColumnA varchar(20),ColumnB varchar(8000))
insert @tb
select ''ProductCode'','+@s2+'
insert @tb
select b.VATProductCode,'+@s3+'
from FS_VATProductCode a,FS_VATProductCustomerCombination b
where a.VATProductCode=b.VATProductCode
group by b.VATProductCode,a.VATProductCodeKey
order by a.VATProductCodeKey
select * from @tb')有一点小出入 第五行 PrimaryTaxCode --Tax
第六行 VATCustomerCodeKey - id倒数第二行 a.VATProductCodeKey --a.id
最末行 a.VATProductCodeKey --a.id
但是无论怎么 ltrim(rtrim(VATCustomerCode))
都没有得到希望得到的结果...
[VATCustomerCode] [char] (5) COLLATE Latin1_General_BIN NOT NULL ,
[VATCustomerCodeKey] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [FS_VATProductCode] (
[VATProductCode] [varchar] (12) COLLATE Latin1_General_BIN NOT NULL ,
[VATProductCodeKey] [int] NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [FS_VATProductCustomerCombination ] (
[PrimaryTaxCode] [char] (2) COLLATE Latin1_General_BIN NOT NULL ,
[VATCustomerCode] [char] (5) COLLATE Latin1_General_BIN NOT NULL ,
[VATProductCode] [varchar] (12) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
GO
[VATCustomerCode] [char] (5) COLLATE Latin1_General_BIN NOT NULL ,
[VATCustomerCodeKey] [int] NOT NULL
) ON [PRIMARY]
insert FS_VATCustomerCode select 'John' ,1
union all select 'Mike' ,2
union all select 'Jully',3
GO
CREATE TABLE [FS_VATProductCode] (
[VATProductCode] [varchar] (12) COLLATE Latin1_General_BIN NOT NULL ,
[VATProductCodeKey] [int] NOT NULL
) ON [PRIMARY]
insert FS_VATProductCode select 'Bike' ,1
union all select 'Car' ,2
union all select 'Tractor',3
GO
CREATE TABLE [FS_VATProductCustomerCombination ] (
[VATCustomerCode] [char] (5) COLLATE Latin1_General_BIN NOT NULL ,
[VATProductCode] [varchar] (12) COLLATE Latin1_General_BIN NOT NULL ,
[PrimaryTaxCode] [char] (2) COLLATE Latin1_General_BIN NOT NULL ) ON [PRIMARY]
insert FS_VATProductCustomerCombination select 'John' ,'Bike' ,'2'
union all select 'John' ,'Car' ,'3'
union all select 'Mike' ,'Tractor','4'
union all select 'Jully','Bike' ,'5'
union all select 'Jully','Car' ,'6'
union all select 'Jully','Tractor','7'
GOdeclare @s2 varchar(8000),@s3 varchar(8000)
select @s2='',@s3=''
select @s2=@s2+'+cast('''' as char(6))+cast('''+rtrim(VATCustomerCode)+''' as char(10))'
,@s3=@s3+'+cast('''' as char(6))+cast(isnull(max(case b.VATCustomerCode when '''
+rtrim(VATCustomerCode)+''' then PrimaryTaxCode end),''FD'') as char(10))'
from FS_VATCustomerCode order by VATCustomerCodeKey
select @s2=stuff(@s2,1,21,''),@s3=stuff(@s3,1,21,'')
exec('
declare @tb table(ColumnA varchar(20),ColumnB varchar(8000))
insert @tb
select ''ProductCode'','+@s2+'
insert @tb
select b.VATProductCode,'+@s3+'
from FS_VATProductCode a,FS_VATProductCustomerCombination b
where a.VATProductCode=b.VATProductCode
group by b.VATProductCode,a.VATProductCodeKey
order by a.VATProductCodeKey
select * from @tb')
go
drop table FS_VATCustomerCode,FS_VATProductCode,FS_VATProductCustomerCombination/*--测试结果ColumnA ColumnB
-------------------- -----------------------------------------
ProductCode John Mike Jully
Bike 2 FD 5
Car 3 FD 6
Tractor FD 4 7
--*/