--測試數據
create table #temp
(
brh varchar(8),
[order_no] varchar(20),
seq int
)
insert into #temp values('3101' , '001',1)
insert into #temp values('3101' , '002',2)
insert into #temp values('3101' , '003',3)
insert into #temp values('3101' , '004',4)
insert into #temp values('3101' , '005',5)
insert into #temp values('3101' , '006',6)
insert into #temp values('3102' , '123',1)
insert into #temp values('3102' , '008',2)
insert into #temp values('3102' , '009',3)
insert into #temp values('3102' , '101',4)
insert into #temp values('3103' , '102',1)
insert into #temp values('3103' , '113',2)
insert into #temp values('3104' , '145',1)
報表內按照brh進行分組,不同組間page break 。同組內顯示全部order_no,同一個組的顯示格式如下。
brh(分店編號);order_no(訂單編號);seq(序列);brh:3101
1)001 2)002 3)003
4)004 5)005 6)006brh:3102
1)123 2)008 3)009
4)101brh:3103
1)102 2)113問題:如何讓同一個組內的數據按照每行只顯示3列,超過3列就折行繼續輸出?,如果有差不多樣式的解決辦法也可以提出。
create table #temp
(
brh varchar(8),
[order_no] varchar(20),
seq int
)
set nocount on
insert into #temp values('3101' , '001',1)
insert into #temp values('3101' , '002',2)
insert into #temp values('3101' , '003',3)
insert into #temp values('3101' , '004',4)
insert into #temp values('3101' , '005',5)
insert into #temp values('3101' , '006',6)
insert into #temp values('3102' , '123',1)
insert into #temp values('3102' , '008',2)
insert into #temp values('3102' , '009',3)
insert into #temp values('3102' , '101',4)
insert into #temp values('3103' , '102',1)
insert into #temp values('3103' , '113',2)
insert into #temp values('3104' , '145',1)
goselect brh,(seq-1)/3+1 as rn,
max(case when seq%3=1 then ltrim(seq)+')' else '' end)
+max(case when seq%3=1 then [order_no]+' ' else '' end)
+max(case when seq%3=2 then ltrim(seq)+')' else '' end)
+max(case when seq%3=2 then [order_no]+' ' else '' end)
+max(case when seq%3=0 then ltrim(seq)+')' else '' end)
+max(case when seq%3=0 then [order_no]+' ' else '' end)
from #temp
group by brh,(seq-1)/3+1
order by brhset nocount off
drop table #temp/*****************brh rn
-------- ----------- ------------------------------------------------------------------------------------------------------
3101 1 1)001 2)002 3)003
3101 2 4)004 5)005 6)006
3102 1 1)123 2)008 3)009
3102 2 4)101
3103 1 1)102 2)113
3104 1 1)145
(
brh varchar(8),
[order_no] varchar(20),
seq int
)
set nocount on
insert into #temp values('3101' , '001',1)
insert into #temp values('3101' , '002',2)
insert into #temp values('3101' , '003',3)
insert into #temp values('3101' , '004',4)
insert into #temp values('3101' , '005',5)
insert into #temp values('3101' , '006',6)
insert into #temp values('3102' , '123',1)
insert into #temp values('3102' , '008',2)
insert into #temp values('3102' , '009',3)
insert into #temp values('3102' , '101',4)
insert into #temp values('3103' , '102',1)
insert into #temp values('3103' , '113',2)
insert into #temp values('3104' , '145',1)
go
select brh,
[order_no]=(select rtrim(seq)+')'+[order_no]+' ' from #temp
where brh=a.brh and (seq-1)/3=(a.seq-1)/3 for xml path(''))
from #temp a group by brh,(seq-1)/3/*
brh order_no
-------- ----------------------
3101 1)001 2)002 3)003
3101 4)004 5)005 6)006
3102 1)123 2)008 3)009
3102 4)101
3103 1)102 2)113
3104 1)145