求一条SQL语句(50分)

求一SQL<br>商品编码 &nbsp; 尺码 &nbsp; &nbsp;数量<br>&nbsp; 2008 &nbsp; &nbsp; &nbsp;S &nbsp; &nbsp; &nbsp; &nbsp;2<br>&nbsp; 2008 &nbsp; &nbsp; &nbsp;L &nbsp; &nbsp; &nbsp; &nbsp;1<br>&nbsp; 2008 &nbsp; &nbsp; &nbsp;X &nbsp; &nbsp; &nbsp; &nbsp;4<br>怎样得出<br>商品编码 &nbsp;S &nbsp;L &nbsp;X &nbsp;M<br>&nbsp; 2008 &nbsp; &nbsp;2 &nbsp;1 &nbsp;4 &nbsp;0

如果是在sql server 可以用case语句，这是个笨方法谁有好的方法请告诉我<br>select 商品编码,sum(sl1) as s,sum(sl2) as l,sum(sl3) as x,sum(sl4) as m from<br>(select 商品编码,case 尺码 when 's' then sl else 0 end as xl1,<br>&nbsp; &nbsp; &nbsp; case 尺码 when 'l' then sl else 0 end as xl2,<br>case 尺码 when 'x' then sl else 0 end as xl3,<br>case 尺码 when 'm' then sl else 0 end as xl4 from xxxxxx)<br>group by 商品编码

case when 實現吧<br>&nbsp;同上，期待更快的方法

似乎这样的问题都是case做的,还未见到别的方法实现的,期待

select 商品编码,<br>sum (case when 尺码='Ｓ' then 数量 else 0 end) as S,<br>sum (case when 尺码='L'　then 数量 else 0 end) as L,<br>sum (case when 尺码='X'　then 数量 else 0 end) as X<br>from table group by 商品编码

同意Corn3

同意楼上

同意Corn3，如果是不确定行的数据转为列则和上面的方法类似，但是要拼成类似上面的语句<br>下面是个类似的行转列的语句<br>declare @str varchar(8000)<br>declare @str1 varchar(8000)<br>declare @kmbh varchar(8000)<br>declare @kmmc varchar(8000)<br>&nbsp;declare @bh int <br>set @str1= ''<br>set @str= ''<br>set @bh= 1 <br>declare &nbsp;fff &nbsp;cursor for select distinct kmbh,kmmc from l_fzzxmtotal where dwmc='部门辅助库-1005AA10000000001HD6' order by kmbh<br>open fff <br>fetch next from fff into @kmbh,@kmmc<br><br>set @str='select dwbh,dwmc, '<br>while @@fetch_status = 0 <br>begin<br>if len(@str) &gt; 7000 <br>&nbsp;begin <br>&nbsp;set @str1 = @str1+@str<br>&nbsp;set @str = ''<br>&nbsp;end <br>&nbsp;else <br>&nbsp;begin <br>&nbsp;set @str=@str+'sum(case when kmbh like &nbsp;'''+@kmbh+'%'' then jfje else 0 &nbsp;end) as '''+cast(@bh as varchar(1000))+''','<br>&nbsp;end <br>&nbsp; set @bh= @bh + 1<br>&nbsp; fetch next from fff into @kmbh,@kmmc<br>end<br>close fff<br>deallocate fff <br>&nbsp;set @str= left(@str,len(@str)-1)<br>set @str=@str+',sum(jfje) je into &nbsp;l_fzzxmtotal_result from l_fzzxmtotal where 1=1' <br>&nbsp;set @str=@str+ ' and dwmc = ''部门辅助库-1005AA10000000001HD6'''<br>&nbsp;set @str=@str+ ' and pzrq &gt;=''20060101'' and pzrq &lt;= ''20061231'''<br>&nbsp;set @str=@str+ ' &nbsp;group by dwbh, dwmc'<br>&nbsp;print @str<br>&nbsp;exec (@str1+@str)

问题解决了access版本的<br>transform num<br>select 商品款号<br>from tmpTable<br>group 商品款号<br>pivot 尺码

晚上结分

典型的行变列问题！

谢谢各位