原始表:
单号----客户-----商品---币种-----金额------规格1-----规格2
1-------A---------SP1----RMB-----100-------GZ1-------GZ2
1-------A---------SP2----USD------200------GZ1-------GZ2
1-------A---------SP3----RMB------300------GZ1-------GZ2
2-------B---------SP1----RMB------150------DD1-------DD2
2-------B---------SP4----USD------190------DD1-------DD2
2-------B---------SP3----USD------250------DD1-------DD2想得到如下查询结果:单号----客户-----币种-----金额---------------备注
1--------A-------RMB------400----------规格1:GZ1,规格2:GZ2
1--------A-------USD------200----------规格1:GZ1,规格2:GZ2,此单号含有美金200元
2--------B-------RMB------150----------规格1:DD1,规格2:DD2
2--------B-------USD------440----------规格1:DD1,规格2:DD2,此单号含有美金150元注:同一单号下的规格都一样,查询结果里面币种如果是USD,则对应备注字段里面要列此单号含有美金xxx元,如果币种是RMB则备注字段没有这句话
谢谢
单号----客户-----商品---币种-----金额------规格1-----规格2
1-------A---------SP1----RMB-----100-------GZ1-------GZ2
1-------A---------SP2----USD------200------GZ1-------GZ2
1-------A---------SP3----RMB------300------GZ1-------GZ2
2-------B---------SP1----RMB------150------DD1-------DD2
2-------B---------SP4----USD------190------DD1-------DD2
2-------B---------SP3----USD------250------DD1-------DD2想得到如下查询结果:单号----客户-----币种-----金额---------------备注
1--------A-------RMB------400----------规格1:GZ1,规格2:GZ2
1--------A-------USD------200----------规格1:GZ1,规格2:GZ2,此单号含有美金200元
2--------B-------RMB------150----------规格1:DD1,规格2:DD2
2--------B-------USD------440----------规格1:DD1,规格2:DD2,此单号含有美金150元注:同一单号下的规格都一样,查询结果里面币种如果是USD,则对应备注字段里面要列此单号含有美金xxx元,如果币种是RMB则备注字段没有这句话
谢谢
--sql2005及以上版本select 单号,客户,币种,sum(金额) as 金额,
stuff((select distinct ','+'规格1:'+规格1+','+'规格2:'+规格2 from 表
where 单号=t.单号 and 客户=t.客户 and 币种=t.币种 for xml path('')),1,1,'')
+isnull(ltrim(nullif(sum(),0)),'') as 备注
from 表 t
group by 单号,客户,币种
可能有误,改改看。
create table 表
(单号 int,客户 varchar(10),商品 varchar(10),币种 varchar(10),金额 int,规格1 varchar(10),规格2 varchar(10))
insert into 表
select 1,'A','SP1','RMB',100,'GZ1','GZ2' union all
select 1,'A','SP2','USD',200,'GZ1','GZ2' union all
select 1,'A','SP3','RMB',300,'GZ1','GZ2' union all
select 2,'B','SP1','RMB',150,'DD1','DD2' union all
select 2,'B','SP4','USD',190,'DD1','DD2' union all
select 2,'B','SP3','USD',250,'DD1','DD2'
goselect 单号,客户,币种,sum(金额) as 金额,
stuff((select distinct ','+'规格1:'+规格1+','+'规格2:'+规格2 from 表
where 单号=t.单号 and 客户=t.客户 and 币种=t.币种 for xml path('')),1,1,'')
+isnull(',此单号含有美金'+ltrim(nullif(sum((case when 币种='RMB' then 0 else 金额 end)),0)),'') as 备注
from 表 t
group by 单号,客户,币种drop table 表/***************************单号 客户 币种 金额 备注
----------- ---------- ---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A RMB 400 规格1:GZ1,规格2:GZ2
1 A USD 200 规格1:GZ1,规格2:GZ2,此单号含有美金200
2 B RMB 150 规格1:DD1,规格2:DD2
2 B USD 440 规格1:DD1,规格2:DD2,此单号含有美金440(4 行受影响)
drop table #tb
Go
Create table #tb([单号] int,[客户] nvarchar(1),[商品] nvarchar(3),[币种] nvarchar(3),[金额] int,[规格1] nvarchar(3),[规格2] nvarchar(3))
Insert #tb
select 1,N'A',N'SP1',N'RMB',100,N'GZ1',N'GZ2' union all
select 1,N'A',N'SP2',N'USD',200,N'GZ1',N'GZ2' union all
select 1,N'A',N'SP3',N'RMB',300,N'GZ1',N'GZ2' union all
select 2,N'B',N'SP1',N'RMB',150,N'DD1',N'DD2' union all
select 2,N'B',N'SP4',N'USD',190,N'DD1',N'DD2' union all
select 2,N'B',N'SP3',N'USD',250,N'DD1',N'DD2'
Go
Select [单号],
[客户],
[币种],
sum([金额])[金额],
N'规格1:'+[规格1]+N'规格2:'+[规格2]+
isnull(N',此单号含有美金'+
ltrim(sum(case when [币种] = N'USD' then [金额] end))+N'元','')
from #tb
group by [单号],[客户],[币种],[规格1],[规格2]
/*
单号 客户 币种 金额
----------- ---- ---- ----------- -----------------------------------
1 A RMB 400 规格1:GZ1规格2:GZ2
1 A USD 200 规格1:GZ1规格2:GZ2,此单号含有美金200元
2 B RMB 150 规格1:DD1规格2:DD2
2 B USD 440 规格1:DD1规格2:DD2,此单号含有美金440元
Warning: Null value is eliminated by an aggregate or other SET operation.(4 row(s) affected)*/
--1-------A---------SP1----RMB-----100-------GZ1-------GZ2
--1-------A---------SP2----USD------200------GZ1-------GZ2
--1-------A---------SP3----RMB------300------GZ1-------GZ2
--2-------B---------SP1----RMB------150------DD1-------DD2
--2-------B---------SP4----USD------190------DD1-------DD2
--2-------B---------SP3----USD------250------DD1-------DD2
if OBJECT_ID('tb')is not null
drop table tb
go
CREATE TABLE tb(单号 int, 客户 varchar(50), 商品 varchar(50), 币种 varchar(50),
金额 int ,规格1 varchar(50), 规格2 varchar(50))
insert into tb values(1, 'A' ,'SP1', 'RMB', 100, 'GZ1', 'GZ2')
insert into tb values(1, 'A' ,'SP2', 'USD', 200, 'GZ1', 'GZ2')
insert into tb values(1, 'A' ,'SP3', 'RMB', 300, 'GZ1', 'GZ2')
insert into tb values(2, 'B' ,'SP1', 'RMB', 150, 'DD1', 'DD2')
insert into tb values(2, 'B' ,'SP3', 'USD', 190, 'DD1', 'DD2')
insert into tb values(2, 'B' ,'SP4', 'USD', 250, 'DD1', 'DD2')--想得到如下查询结果:--单号----客户-----币种-----金额---------------备注
--1--------A-------RMB------400----------规格1:GZ1,规格2:GZ2
--1--------A-------USD------200----------规格1:GZ1,规格2:GZ2,此单号含有美金200元
--2--------B-------RMB------150----------规格1:DD1,规格2:DD2
--2--------B-------USD------440----------规格1:DD1,规格2:DD2,此单号含有美金150元
;with ct
as
(
select 单号,客户,币种 ,sum(金额)as 金额 ,
stuff( (select distinct ','+'规格1:'+规格1+','+'规格2:'+规格2 from tb
where 单号=t.单号 and 客户=t.客户 and 币种=t.币种 for xml path('')),1,1,'')
as 备注
from tb t group by 单号,客户,币种 )select 单号, 客户, 币种 , 金额 ,
备注+(case when 币种='USD' then '此单号含有美金'+ cast(金额 AS varchar(50)) +'元' else '' end)
from ct
单号 客户 币种 金额
----------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A RMB 400 规格1:GZ1,规格2:GZ2
1 A USD 200 规格1:GZ1,规格2:GZ2此单号含有美金200元
2 B RMB 150 规格1:DD1,规格2:DD2
2 B USD 440 规格1:DD1,规格2:DD2此单号含有美金440元(4 行受影响)
--1-------A---------SP1----RMB-----100-------GZ1-------GZ2
--1-------A---------SP2----USD------200------GZ1-------GZ2
--1-------A---------SP3----RMB------300------GZ1-------GZ2
--2-------B---------SP1----RMB------150------DD1-------DD2
--2-------B---------SP4----USD------190------DD1-------DD2
--2-------B---------SP3----USD------250------DD1-------DD2
if OBJECT_ID('tb')is not null
drop table tb
go
CREATE TABLE tb(单号 int, 客户 varchar(50), 商品 varchar(50), 币种 varchar(50),
金额 int ,规格1 varchar(50), 规格2 varchar(50))
insert into tb values(1, 'A' ,'SP1', 'RMB', 100, 'GZ1', 'GZ2')
insert into tb values(1, 'A' ,'SP2', 'USD', 200, 'GZ1', 'GZ2')
insert into tb values(1, 'A' ,'SP3', 'RMB', 300, 'GZ1', 'GZ2')
insert into tb values(2, 'B' ,'SP1', 'RMB', 150, 'DD1', 'DD2')
insert into tb values(2, 'B' ,'SP3', 'USD', 190, 'DD1', 'DD2')
insert into tb values(2, 'B' ,'SP4', 'USD', 250, 'DD1', 'DD2')--想得到如下查询结果:--单号----客户-----币种-----金额---------------备注
--1--------A-------RMB------400----------规格1:GZ1,规格2:GZ2
--1--------A-------USD------200----------规格1:GZ1,规格2:GZ2,此单号含有美金200元
--2--------B-------RMB------150----------规格1:DD1,规格2:DD2
--2--------B-------USD------440----------规格1:DD1,规格2:DD2,此单号含有美金150元
select 单号, 客户, 币种 , 金额 ,
备注+(case when 币种='USD' then '此单号含有美金'+ cast(金额 AS varchar(50)) +'元' else '' end)
from (
select 单号,客户,币种 ,sum(金额)as 金额 ,
stuff( (select distinct ','+'规格1:'+规格1+','+'规格2:'+规格2 from tb
where 单号=t.单号 and 客户=t.客户 and 币种=t.币种 for xml path('')),1,1,'')
as 备注
from tb t group by 单号,客户,币种 )a
(1 行受影响)
单号 客户 币种 金额
----------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A RMB 400 规格1:GZ1,规格2:GZ2
1 A USD 200 规格1:GZ1,规格2:GZ2此单号含有美金200元
2 B RMB 150 规格1:DD1,规格2:DD2
2 B USD 440 规格1:DD1,规格2:DD2此单号含有美金440元(4 行受影响)