100分求使用SQL实现一个比较复杂的统计问题,请来看看...
产品ID       规格                           数量
1                 1.0*2.0*6000           1
2                 1.0*2.0*6000           1
3                 1.1*2.0*6000           1
4                 1.2*3.2*6000           1

统计结果：

              1.0         1.1         1.2                
2.0         2             1        
3.2                                     1  




------解决方案--------------------
ALTER PROCEDURE dbo.stat

AS
SET NOCOUNT ON
select 产品ID,left(规格,3) as L,right(left(规格,7),3) as W, right(规格,4) as H ,数量 into #T from [product]
declare @sql varchar(1000)
select L,Sum(total) as LC into #t1 from #t group by L
set @sql = 'select W '
select @sql = @sql + ',sum(case when L= ' ' ' + L + ' ' ' then total else 0 end) as ' ' ' + L + ' ' ' ' from #t1
set @sql = @sql + ' from #t Group by w '
execute(@sql)
RETURN



*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)

http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
------解决方案--------------------
Create table test(产品ID int,规格 varchar(20),数量 int)

insert into test
Select 1, '1.0*2.0*6000 ',1 union all
Select 2, '1.0*2.0*6000 ',1 union all
Select 3, '1.1*2.0*6000 ',1 union all
Select 4, '1.2*3.2*6000 ',1


Declare @s1 varchar(800),@s2 varchar(1000)
Select @s1= ' ',@s2= ' '
Select @s1=@s1+ ',[ '+ left(规格,3) + ']=sum(case when left(规格,3)= ' ' ' + left(规格,3) + ' ' ' AND SUBSTRING(规格,5,3)= ' ' ' + SUBSTRING(规格,5,3) + ' ' ' then 1 end) '
from test Group By 规格
Select @s2= 'Select SUBSTRING(规格,5,3) '+@s1 + ' From test GRoup By SUBSTRING(规格,5,3) '
exec(@s2)