CREATE TABLE 表1( 商品名 varchar(20), 数量 float, 尺寸 varchar(20) )GOINSERT INTO 表1 VALUES ('品名1', 10, '小')INSERT INTO 表1 VALUES ('品名1', 10, '小')INSERT INTO 表1 VALUES ('品名1', 20, '中')INSERT INTO 表1 VALUES ('品名1', 30, '大')INSERT INTO 表1 VALUES ('品名1', 40, '加大')INSERT INTO 表1 VALUES ('品名2', 11, '11CMX55CM')INSERT INTO 表1 VALUES ('品名2', 21, '12CMX55CM')INSERT INTO 表1 VALUES ('品名2', 31, '13CMX55CM')INSERT INTO 表1 VALUES ('品名2', 41, '14CMX55CM')INSERT INTO 表1 VALUES ('品名2', 51, '15CMX55CM')GO=======================================================================
要做成如下的报表(FR) 小 中 大 加大品名1 --------------------------------------------------- 20 20 30 40 11CMX55CM 12CMX55CM 13CMX55CM 14CMX55CM 15CMX55CM品名1 ----------------------------------------------------------------- 11 21 31 41 51
要做成如下的报表(FR) 小 中 大 加大品名1 --------------------------------------------------- 20 20 30 40 11CMX55CM 12CMX55CM 13CMX55CM 14CMX55CM 15CMX55CM品名1 ----------------------------------------------------------------- 11 21 31 41 51
不过你可以写两个SQL语句
select 商品名,(case when 尺寸=‘小’ then sum(数量) else 0) as 小,
(case when 尺寸=‘中’ then sum(数量) else 0) as 中,
(case when 尺寸=‘大’ then sum(数量) else 0) as 大,
(case when 尺寸=‘加大’ then sum(数量) else 0) as 加大,
(case when 尺寸=‘11CMX55CM' then sum(数量) else 0) as 11CMX55CM,
(case when 尺寸=‘12CMX55CM’ then sum(数量) else 0) as 12CMX55CM,
(case when 尺寸=‘13CMX55CM’ then sum(数量) else 0) as 13CMX55CM,
(case when 尺寸=‘14CMX55CM’ then sum(数量) else 0) as 14CMX55CM,
(case when 尺寸=‘15CMX55CM’ then sum(数量) else 0) as 15CMX55CM
from Table
SET @SQL='SELECT 商品名'
SELECT @SQL= @SQL+ ',MIN(CASE WHEN 尺寸 = ''' + 尺寸 + ''' THEN 数量 END) [' + 尺寸 + ']' FROM (SELECT DISTINCT 尺寸 FROM 表1) A
SET @SQL=@SQL+ ' FROM (select 商品名,sum(数量) as 数量,尺寸 from 表1 GROUP BY 商品名,尺寸) b GROUP BY 商品名'
EXEC (@SQL)
DECLARE @SQL VARCHAR(8000)
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @maxcol integer
declare @Itemname varchar(255)
declare @gt table (商品名 varchar(100),数量 integer,尺寸 varchar(255))
declare @gc table (商品名 varchar(100),cc integer)insert into @gt select 商品名,sum(数量) as 数量,尺寸 from 表1 GROUP BY 商品名,尺寸
insert into @gc select 商品名,count(*) as cc from @gt group by 商品名select @maxcol=max(cc) from @gc bb
DECLARE Itemname_cursor CURSOR for select 商品名 from @gcSET @SQL=''
open Itemname_cursorFetch next From Itemname_cursor into @Itemname
While @@FETCH_STATUS =0
begin
if @SQL<>'' set @SQL=@SQL+' union '
SET @SQL1='SELECT 商品名,商品名+''1'' as Orders'
SELECT @SQL1= @SQL1+ ',MIN(CASE WHEN 尺寸 = ''' + 尺寸 + ''' THEN 数量 END) '
FROM (SELECT DISTINCT 尺寸 FROM 表1 where 商品名=@Itemname) A
select @SQL1=@SQL1+REPLICATE(','+char(39)+char(39),@maxcol-cc) from @gc where 商品名=@Itemname
SET @SQL1=@SQL1+ ' FROM (select 商品名,cast(sum(数量) as char(20))as 数量,尺寸 from 表1 where 商品名='''+@Itemname+''' GROUP BY 商品名,尺寸 ) b GROUP BY 商品名'
SET @SQL2='SELECT '''',商品名+''0'' as Orders'
SELECT @SQL2= @SQL2+ ',MIN(CASE WHEN 尺寸 = ''' + 尺寸 + ''' THEN 尺寸 END) '
FROM (SELECT DISTINCT 尺寸 FROM 表1 where 商品名=@Itemname) A
select @SQL2=@SQL2+REPLICATE(','+char(39)+char(39),@maxcol-cc) from @gc where 商品名=@ItemnameSET @SQL2=@SQL2+ ' FROM (select 商品名,尺寸 from 表1 where 商品名='''+@Itemname+''' GROUP BY 商品名,尺寸 ) b GROUP BY 商品名'
Set @SQL=@SQL+@SQL2+' union '+@SQL1 Fetch next From Itemname_cursor into @Itemname
endClose Itemname_cursor
Deallocate Itemname_cursorset @SQL=@SQL+' order by orders'exec (@SQL)
品名1 品名11 30 40 20 20
品名20 11CMX55CM 12CMX55CM 13CMX55CM 14CMX55CM 15CMX55CM
品名2 品名21 11 21 31 41 51
品名1 品名11 30 40 20 20
品名20 11CMX55CM 12CMX55CM 13CMX55CM 14CMX55CM 15CMX55CM
品名2 品名21 11 21 31 41 51
怎么会变形的,效果
http://blog.csdn.net/images/blog_csdn_net/jinjazz/67499/r_fr.jpg