跪求一SQL语句!

[MINDNET]

原始数据如下:
ussion_date size_mark qty uom
2005-02-23 S 22330 pcs
2005-02-23 XS 38730 pcs
2005-02-23 M 44710 pcs
2005-02-23 L 38150 pcs
2005-04-07 S 22900 pcs
2005-04-07 XS 11480 pcs
2005-04-07 M 17600 pcs
2005-05-04 S 11330 pcs
2005-05-04 M 22130 pcs
现要做成这样
ussion_daten XS S M L uom
2005-02-23 38730 22330 44710 38150 pcs
2005-04-07 11480 22900 17600 0 pcs
2005-05-04 0 11330 22130 0 pcs
其中size_mark是随意的可能只是XS,S,M,L 也可以有XS,S,M,L,XXS就是说列数是可变的,望各位大哥指教.

[jinjazz]

--建立测试环境
Create Table 表(ussion_date varchar(10),size_mark varchar(10),qty integer,uom varchar(10))
--插入数据
insert into 表
select '2005-02-23','S','22330','pcs' union
select '2005-02-23','XS','38730','pcs' union
select '2005-02-23','M','44710','pcs' union
select '2005-02-23','L','38150','pcs' union
select '2005-04-07','S','22900','pcs' union
select '2005-04-07','XS','11480','pcs' union
select '2005-04-07','M','17600','pcs' union
select '2005-05-04','S','11330','pcs' union
select '2005-05-04','M','22130','pcs'
--测试语句
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT ussion_date,uom'
SELECT @SQL= @SQL+ ',isnull(sum(CASE WHEN size_mark = ''' + size_mark + ''' THEN qty END),0) [' + size_mark + ']' FROM (SELECT DISTINCT size_mark FROM 表) A
SET @SQL=@SQL+' FROM 表 GROUP BY ussion_date,uom'
EXEC (@SQL)

--删除测试环境
Drop Table 表

/*-----测试结果

ussion_date uom L M S XS
----------- ---------- ----------- ----------- ----------- -----------
2005-02-23 pcs 38150 44710 22330 38730
2005-04-07 pcs 0 17600 22900 11480
2005-05-04 pcs 0 22130 11330 0
------*/

[MINDNET]

先试试如果成功马上给分

[samfeng_2003]

declare @s varchar(2000)
select @s=''
select @s = @s + ','+size_mark+' = isnull(sum(case when size_mark = '''+size_mark+''' then qty end),0)'
from 表
group by size_mark order by size_mark desc

exec('select ussion_date'+@s+',uom from 表 group by ussion_date,uom')

[sduffer]

如果不嫌麻烦的话也可以用动态产生表的方法来实现.使用光标来建立表,
然后将数据对应字段写入, 做后查询返回结果