有一数据表,
bpno wh qty
a R1 1
a R2 1
a R3 1
a R4 1
b R5 1
要求得到以下结果:
bpno qty wh
a 3 R1,R2,R3
a 1 R4
b 1 R5
以上每三个相同的bpno,用'组成一行,超过则换行谢谢!
bpno wh qty
a R1 1
a R2 1
a R3 1
a R4 1
b R5 1
要求得到以下结果:
bpno qty wh
a 3 R1,R2,R3
a 1 R4
b 1 R5
以上每三个相同的bpno,用'组成一行,超过则换行谢谢!
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (bpno varchar(1),wh varchar(2),qty int)
insert into #T
select 'a','R1',1 union all
select 'a','R2',1 union all
select 'a','R3',1 union all
select 'a','R4',1 union all
select 'b','R5',1;with T as
(
select id=row_number()over(partition by bpno order by wh),* from #T
)
select bpno, qty=sum(qty), wh=(stuff((select ','+wh from T where bpno=a.bpno and (id-1)/3=(a.id-1)/3 for xml path('')),1,1,'')) from T a group by bpno, (id-1)/3 order by 1/*
bpno qty wh
---- ----------- -----------
a 3 R1,R2,R3
a 1 R4
b 1 R5
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (bpno varchar(1),wh varchar(2),qty int)
insert into #T
select 'a','R1',1 union all
select 'a','R2',1 union all
select 'a','R3',1 union all
select 'a','R4',1 union all
select 'b','R5',1--> 2000
if object_id('tempdb.dbo.#') is not null drop table #
select id=cast(null as int),*,id1=identity(int,1,1) into # from #T order by bpno,wh
update # set id=id1-(select min(id1)-1 from # where bpno=t.bpno) from # as tselect
bpno,
qty=sum(qty),
wh=max(case (id-1)%3 when 0 then wh else '' end)+max(case (id-1)%3 when 1 then ','+wh else '' end)+max(case (id-1)%3 when 2 then ','+wh else '' end)
from # group by bpno, (id-1)/3 order by 1/*
bpno qty wh
---- ----------- --------
a 3 R1,R2,R3
a 1 R4
b 1 R5
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (bpno varchar(1),wh varchar(2),qty int)
insert into #T
select 'a','R1',1 union all
select 'a','R2',1 union all
select 'a','R3',1 union all
select 'a','R4',1 union all
select 'b','R5',1--> 2000
if object_id('tempdb.dbo.#') is not null drop table #
select id=cast(null as int),*,id1=identity(int,1,1) into # from #T order by bpno,wh
--update # set id=id1-(select min(id1)-1 from # where bpno=t.bpno) from # as t
update # set id=id1-(select min(id1) from # where bpno=t.bpno) from # as t --> 将最小id变为0,下面不用(id-1)了。select
bpno,
qty=sum(qty),
wh=max(case id%3 when 0 then wh else '' end)+max(case id%3 when 1 then ','+wh else '' end)+max(case id%3 when 2 then ','+wh else '' end)
from # group by bpno, id/3 order by 1/*
bpno qty wh
---- ----------- --------
a 3 R1,R2,R3
a 1 R4
b 1 R5
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (bpno varchar(1),wh varchar(2),qty int)
insert into #T
select 'a','R1',1 union all
select 'a','R2',1 union all
select 'a','R3',1 union all
select 'a','R4',1 union all
select 'a','',1 union all
select 'a',null,1 union all
select 'b','R5',1--> 2000
if object_id('tempdb.dbo.#') is not null drop table #
select id=cast(null as int),*,id1=identity(int,1,1) into # from #T order by bpno --> 有NULL和空串,干脆不对wh排序了
update # set id=id1-(select min(id1) from # where bpno=t.bpno) from # as t --> 将最小id变为0,下面不用(id-1)了。select bpno,qty,wh=left(wh,len(wh)-1) from
(
select
bpno,
qty=sum(qty),
wh=max(case id%3 when 0 then isnull(nullif(wh+',',','),'') else '' end)+
max(case id%3 when 1 then isnull(nullif(wh+',',','),'') else '' end)+
max(case id%3 when 2 then isnull(nullif(wh+',',','),'') else '' end)
from # group by bpno, id/3
) as t
order by 1/*
bpno qty wh
---- ----------- ---------
a 3 R1,R2,R3
a 3 R4
b 1 R5
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (bpno varchar(1),wh varchar(2),qty int)
insert into #T
select 'a','R1',1 union all
select 'a','R2',1 union all
select 'a','R3',1 union all
select 'a','',1 union all
select 'a','',1 union all
select 'a',null,1 union all
select 'b','R5',1--> 2000
if object_id('tempdb.dbo.#') is not null drop table #
select id=cast(null as int),*,id1=identity(int,1,1) into # from #T order by bpno --> 有NULL和空串,干脆不对wh排序了
update # set id=id1-(select min(id1) from # where bpno=t.bpno) from # as t --> 将最小id变为0,下面不用(id-1)了。-->刚才极端情况没考虑,应该这样:
select bpno,qty,wh=left(wh,isnull(nullif(len(wh),0),1)-1) from
(
select
bpno,
qty=sum(qty),
wh=max(case id%3 when 0 then isnull(nullif(wh+',',','),'') else '' end)+
max(case id%3 when 1 then isnull(nullif(wh+',',','),'') else '' end)+
max(case id%3 when 2 then isnull(nullif(wh+',',','),'') else '' end)
from # group by bpno, id/3
) as t
order by 1/*
bpno qty wh
---- ----------- ---------
a 3 R1,R2,R3
a 3
b 1 R5
*/