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

Question

  型号           规格         净重
 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个 重量，超过的另外起一行  ！不足的就一行！

Answer 1

if object_id('tb') is not null
drop table tb
gocreate 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

Answer 2

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

Answer 3

if object_id('tb') is not null
    drop table tb
gocreate 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 行受影响)*/

Answer 4

楼上的大哥，你的方法好像有不严谨的地方，我带入到我的数据库以后 出现以下的问题 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

Answer 5

[Quote=引用 4 楼 feixianxxx 的回复:]
SQL code
if object_id('tb') is not null
    drop table tb
gocreate table tb(型号 varchar(10),规格 varchar(10),净重 numeric(4,1))
insert tb select 'bopp光膜','18*600',105.1
union all select 'bopp光膜','……
[/Quote对不起，是我搞错了！

Answer 6

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