一个困扰我多是的sql 问题，期待高手支招！

[greatwall8848]

型号 规格 净重
bopp光膜 18*600 105.1
bopp光膜 18*600 105.2
bopp光膜 18*600 105.3
bopp光膜 18*600 105.4
bopp光膜 18*600 105.5
bopp光膜 18*600 105.6
bopp光膜 18*600 105.7
bopp光膜 18*600 105.8
bopp光膜 18*600 105.4
bopp光膜 18*600 105.6
bopp光膜 18*600 105.1
bopp光膜 18*600 105.2

bopp光膜 18*610 105.1
bopp光膜 18*610 105.1
bopp光膜 18*610 105.2
bopp光膜 18*620 105.1
bopp光膜 18*620 105.4
bopp光膜 18*800 105.1
bopp光膜 18*800 105.8
bopp光膜 18*800 105.1




变成 这种格式

型号 规格 重量明细 件数 合计重量
bopp光膜 18*600 105.1 105.2 105.3 105.4 105.5,105.6,105.7 ,105.8 8
bopp光膜 18*600 105.4 105.6 105.1 105.2 4
bopp光膜 18*610 105.1 105.1 105.2 3
bopp光膜 18*620 105.1 105.4 2
bopp光膜 18*800 105.1 105.8 105.1 3


就是说 每个规格 最多一行出现8个 重量，超过的另外起一行 ！不足的就一行！

[richgong]

行轉列.
csdn有.類似解答..

[greatwall8848]

二楼的太厉害了！ 一句就搞定了，但是我发贴的时候有个问题没有说清楚 ，重量明细里面的8个是8个单独的字段，应该比如 重量1、重量2、重量3、重量4、重量5、重量6、重量7、重量8

[feixianxxx]

if object_id('tb') is not null
    drop table tb
go

create table tb(型号 varchar(10),规格 varchar(10),净重 numeric(4,1))
insert tb select 'bopp光膜','18*600',105.1
union all select 'bopp光膜','18*600',105.2
union all select 'bopp光膜','18*600',105.3
union all select 'bopp光膜','18*600',105.4
union all select 'bopp光膜','18*600',105.5
union all select 'bopp光膜','18*600',105.6
union all select 'bopp光膜','18*600',105.7
union all select 'bopp光膜','18*600',105.8
union all select 'bopp光膜','18*600',105.4
union all select 'bopp光膜','18*600',105.6
union all select 'bopp光膜','18*600',105.1
union all select 'bopp光膜','18*600',105.2
union all select 'bopp光膜','18*610',105.1
union all select 'bopp光膜','18*610',105.1
union all select 'bopp光膜','18*610',105.2
union all select 'bopp光膜','18*620',105.1
union all select 'bopp光膜','18*620',105.4
union all select 'bopp光膜','18*800',105.1
union all select 'bopp光膜','18*800',105.8
union all select 'bopp光膜','18*800',105.1
go
with cte as
(
	select rn= ROW_NUMBER()over(PARTITION by 型号,规格 order by getdate()),* 
	from tb 
)
select 型号,规格,
重量1=max(case when rn%8=1 then 净重 end),
重量2=max(case when rn%8=2 then 净重 end),
重量3=max(case when rn%8=3 then 净重 end),
重量4=max(case when rn%8=4 then 净重 end),
重量5=max(case when rn%8=5 then 净重 end),
重量6=max(case when rn%8=6 then 净重 end),
重量7=max(case when rn%8=7 then 净重 end),
重量8=max(case when rn%8=0 then 净重 end),
件数=COUNT(*),
合计重量=SUM(净重)
from cte 
group by 型号,规格,(rn-1)/8
/*
型号         规格         重量1                                     重量2                                     重量3                                     重量4                                     重量5                                     重量6                                     重量7                                     重量8                                     件数          合计重量
---------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- ---------------------------------------
bopp光膜     18*600     105.1                                   105.2                                   105.3                                   105.4                                   105.5                                   105.6                                   105.7                                   105.8                                   8           843.6
bopp光膜     18*600     105.4                                   105.6                                   105.1                                   105.2                                   NULL                                    NULL                                    NULL                                    NULL                                    4           421.3
bopp光膜     18*610     105.1                                   105.1                                   105.2                                   NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    3           315.4
bopp光膜     18*620     105.1                                   105.4                                   NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    2           210.5
bopp光膜     18*800     105.1                                   105.8                                   105.1                                   NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    3           316.0
警告: 聚合或其他 SET 操作消除了 Null 值。

(5 行受影响)

*/

[liyaohui13098452233]

呵呵……

[greatwall8848]

楼上的大哥，你的方法好像有不严谨的地方，我带入到我的数据库以后 出现以下的问题 BOPP消光膜 15*1200 1200.0 1200.0 1200.0 1200.0 1200.0 1200.0 1200.0 1200.0 8 9600.0
BOPP消光膜 15*1200 1200.0 1200.0 NULL NULL NULL NULL NULL NULL 2 2400.0
BOPP消光膜 18*1000 1000.0 1000.0 1000.0 1000.0 1000.0 1000.0 1000.0 1000.0 8 8000.0
BOPP消光膜 18*1000 1000.0 1000.0 1000.0 1000.0 1000.0 1000.0 1000.0 NULL 7 7000.0
BOPP消光膜 18*1200 1200.0 1200.0 1200.0 1200.0 1200.0 1200.0 1200.0 1200.0 8 9600.0
BOPP消光膜 18*1200 1200.0 1200.0 1200.0 1200.0 1200.0 1200.0 1200.0 1200.0 8 9600.0
BOPP消光膜 18*340 340.0 340.0 340.0 340.0 340.0 NULL NULL NULL 5 1700.0
BOPP消光膜 18*350 350.0 350.0 350.0 350.0 350.0 NULL NULL NULL 5 1750.0
BOPP消光膜 18*370 370.0 370.0 370.0 370.0 370.0 370.0 NULL NULL 6 2220.0
BOPP消光膜 18*770 770.0 770.0 770.0 770.0 770.0 770.0 770.0 770.0 8 6160.0
BOPP消光膜 18*770 770.0 770.0 770.0 770.0 770.0 770.0 770.0 770.0 8 6160.0
BOPP消光膜 18*770 770.0 NULL NULL NULL NULL NULL NULL NULL 1 770.0

[greatwall8848]

18*1200 数量为8 的两行都重复出现一次！

[feixianxxx]

[Quote=引用 7 楼 greatwall8848 的回复:]

18*1200 数量为8 的两行都重复出现一次！
[/Quote]
...分组怎么可能出现重复....

仔细检查下 我自己这测试没有问题

[zarejn]

DDDDDDDDDDDDDDDDDD

[greatwall8848]

[Quote=引用 4 楼 feixianxxx 的回复:]
SQL code
if object_id('tb') is not null
drop table tb
go

create table tb(型号 varchar(10),规格 varchar(10),净重 numeric(4,1))
insert tb select 'bopp光膜','18*600',105.1
union all select 'bopp光膜','……
[/Quote



对不起，是我搞错了！

[greatwall8848]

二楼的程序有问题，会漏掉一些数据！

[greatwall8848]

csdn的兄弟太热情了，放分！谢谢！

[bill024]

if object_id('tb') is not null
	drop table tb
go

create table tb(型号 varchar(10),规格 varchar(10),净重 numeric(4,1))
insert tb select 'bopp光膜','18*600',105.1
union all select 'bopp光膜','18*600',105.2
union all select 'bopp光膜','18*600',105.3
union all select 'bopp光膜','18*600',105.4
union all select 'bopp光膜','18*600',105.5
union all select 'bopp光膜','18*600',105.6
union all select 'bopp光膜','18*600',105.7
union all select 'bopp光膜','18*600',105.8
union all select 'bopp光膜','18*600',105.4
union all select 'bopp光膜','18*600',105.6
union all select 'bopp光膜','18*600',105.1
union all select 'bopp光膜','18*600',105.2
union all select 'bopp光膜','18*610',105.1
union all select 'bopp光膜','18*610',105.1
union all select 'bopp光膜','18*610',105.2
union all select 'bopp光膜','18*620',105.1
union all select 'bopp光膜','18*620',105.4
union all select 'bopp光膜','18*800',105.1
union all select 'bopp光膜','18*800',105.8
union all select 'bopp光膜','18*800',105.1

;with test1 as
(
	select id=row_number() over(partition by 型号,规格 order by getdate()),型号,规格,净重 from tb
)
,test2 as
(
	select id,id1=row_number() over(partition by 型号,规格,(id-1)/8 order by getdate()),型号,规格,净重 from test1
)
,test3 as
(
	select id,id1,型号,规格,净重,件数=count(*) over(partition by 型号,规格,id-id1),合计=sum(净重) over(partition by 型号,规格,id-id1) from test2
)
select distinct 型号,规格,重量明细=stuff((select ','+ltrim(净重) from test3 where 型号=t.型号 and 规格=t.规格 and id-id1=t.id-t.id1 for xml path('')),1,1,''),
件数,合计 from test3 t

型号		规格	重量明细										件数合计
bopp光膜	18*600	105.1,105.2,105.3,105.4,105.5,105.6,105.7,105.8	8	843.6
bopp光膜	18*600	105.4,105.6,105.1,105.2							4	421.3
bopp光膜	18*610	105.1,105.1,105.2								3	315.4
bopp光膜	18*620	105.1,105.4										2	210.5
bopp光膜	18*800	105.1,105.8,105.1								3	316.0