把这种查询效果商品号 商品名 尺码 价格
001 XX鞋 ss $1
s $1
M $1.1
L $1.1
LL $1.2
002 YY鞋 ss $1
s $1
M $1.1
L $1.1
LL $1.2
007 Y衬衫 SS $2
s $2
M $2.1
L $2.1
LL $2.5
011 商品1 $2
037 帽子2 $2
091 领带 $2
037 XX2 $2
099 XXX $2-----------------------------
要变成这种效果
↓↓↓↓↓
---------------------------------商品号 商品名 尺码 价格 商品号 商品名 尺码 价格
001 XX鞋 ss $1 007 Y衬衫 SS $2
s $1 s $2
M $1.1 M $2.1
L $1.1 L $2.1
LL $1.2 LL $2.5
002 YY鞋 ss $1 011 商品1 $2
s $1 037 帽子2 $2
M $1.1 091 领带 $2
L $1.1 037 XX2 $2
LL $1.2 099 XX2 $2
求教达人。
001 XX鞋 ss $1
s $1
M $1.1
L $1.1
LL $1.2
002 YY鞋 ss $1
s $1
M $1.1
L $1.1
LL $1.2
007 Y衬衫 SS $2
s $2
M $2.1
L $2.1
LL $2.5
011 商品1 $2
037 帽子2 $2
091 领带 $2
037 XX2 $2
099 XXX $2-----------------------------
要变成这种效果
↓↓↓↓↓
---------------------------------商品号 商品名 尺码 价格 商品号 商品名 尺码 价格
001 XX鞋 ss $1 007 Y衬衫 SS $2
s $1 s $2
M $1.1 M $2.1
L $1.1 L $2.1
LL $1.2 LL $2.5
002 YY鞋 ss $1 011 商品1 $2
s $1 037 帽子2 $2
M $1.1 091 领带 $2
L $1.1 037 XX2 $2
LL $1.2 099 XX2 $2
求教达人。
/******************************************************************************/
/*回复:20080610001总:00064 */
/*主题:数据显示 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @sp table([商品号] varchar(10),[商品名] varchar(15),[尺码] varchar(8),[价格] varchar(6))
insert into @sp select '001','XX鞋','ss','$1'
insert into @sp select '','','s','$1'
insert into @sp select '','','M','$1.1'
insert into @sp select '','','L','$1.1'
insert into @sp select '','','LL','$1.2'
insert into @sp select '002','YY鞋','ss','$1'
insert into @sp select '','','s','$1'
insert into @sp select '','','M','$1.1'
insert into @sp select '','','L','$1.1'
insert into @sp select '','','LL','$1.2'
insert into @sp select '007','Y衬衫','SS','$2'
insert into @sp select '','','s','$2'
insert into @sp select '','','M','$2.1'
insert into @sp select '','','L','$2.1'
insert into @sp select '','','LL','$2.5'
insert into @sp select '011','商品1','','$2'
insert into @sp select '037','帽子2','','$2'
insert into @sp select '091','领带','','$2'
insert into @sp select '037','XX2','','$2'
insert into @sp select '099','XXX','','$2'--代码--------------------------------------------------------------------------
declare @t table(id int identity,bh varchar(10),mc varchar(20),cm varchar(8),jg varchar(8))
insert @t(bh,mc,cm,jg) select * from @sp
select a.bh 商品号,a.mc 商品名,a.cm 尺码,a.jg 价格,b.bh 商品号,b.mc 商品名,b.cm 尺码,b.jg 价格 from @t a left join @t b on a.id = b.id-(@@rowcount+1)/2
where a.id <=(@@rowcount+1)/2
go/*结果--------------------------------------------------------------------------
商品号 商品名 尺码 价格 商品号 商品名 尺码 价格
---------- -------------------- -------- -------- ---------- -------------------- -------- --------
001 XX鞋 ss $1 007 Y衬衫 SS $2
s $1 s $2
M $1.1 M $2.1
L $1.1 L $2.1
LL $1.2 LL $2.5
002 YY鞋 ss $1 011 商品1 $2
s $1 037 帽子2 $2
M $1.1 091 领带 $2
L $1.1 037 XX2 $2
LL $1.2 099 XXX $2
--清除------------------------------------------------------------------------*/
/******************************************************************************/
/*回复:20080610001总:00064 */
/*主题:数据显示 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
declare @sp table([商品号] varchar(10),[商品名] varchar(15),[尺码] varchar(8),[价格] varchar(6))
insert into @sp select '001','XX鞋','ss','$1'
insert into @sp select '','','s','$1'
insert into @sp select '','','M','$1.1'
insert into @sp select '','','L','$1.1'
insert into @sp select '','','LL','$1.2'
insert into @sp select '002','YY鞋','ss','$1'
insert into @sp select '','','s','$1'
insert into @sp select '','','M','$1.1'
insert into @sp select '','','L','$1.1'
insert into @sp select '','','LL','$1.2'
insert into @sp select '007','Y衬衫','SS','$2'
insert into @sp select '','','s','$2'
insert into @sp select '','','M','$2.1'
insert into @sp select '','','L','$2.1'
insert into @sp select '','','LL','$2.5'
insert into @sp select '011','商品1','','$2'
insert into @sp select '037','帽子2','','$2'
insert into @sp select '091','领带','','$2'
insert into @sp select '037','XX2','','$2'
insert into @sp select '099','XXX','','$2'
insert into @sp select '199','XX3','','$3'--代码--------------------------------------------------------------------------
declare @t table(id int identity,bh varchar(10),mc varchar(20),cm varchar(8),jg varchar(8))
declare @i int
insert @t(bh,mc,cm,jg) select * from @sp
set @i = @@rowcount
update a set bh=b.bh,mc = b.mc from @t a,
(select bh,mc from @t where id = (select max(id) from @t where id<=(@i+1)/2 and bh<>'')) b
where a.id = (@i+1)/2+1 and a.bh=''
select a.bh 商品号,a.mc 商品名,a.cm 尺码,a.jg 价格,b.bh 商品号,b.mc 商品名,b.cm 尺码,b.jg 价格 from @t a left join @t b on a.id = b.id-(@i+1)/2
where a.id <=(@i+1)/2
go/*结果--------------------------------------------------------------------------
品号 商品名 尺码 价格 商品号 商品名 尺码 价格
---------- -------------------- -------- -------- ---------- -------------------- -------- --------
001 XX鞋 ss $1 007 Y衬衫 s $2
s $1 M $2.1
M $1.1 L $2.1
L $1.1 LL $2.5
LL $1.2 011 商品1 $2
002 YY鞋 ss $1 037 帽子2 $2
s $1 091 领带 $2
M $1.1 037 XX2 $2
L $1.1 099 XXX $2
LL $1.2 199 XX3 $3
007 Y衬衫 SS $2 NULL NULL NULL NULL
--清除------------------------------------------------------------------------*/
我编辑好的格式,一发布就变的乱糟糟的,
辛苦大家的眼睛了。谢谢Herb2
谢谢wh110
谢谢roy_88
谢谢CCTV
谢谢MTV
......回复的真快,谢谢。回roy_88
其实这个不是SQLServer的
是ACCESS的,
很小的一个系统,数据库不大,
用VBA作,
要求报表显示的样式是这样如果不用查询作,
谁能够让报表显示成这样,
我另外给200分。