一个行转列的sql语句 ( 积分: 100 )

有一个表：(id工号&nbsp;&nbsp;month月份&nbsp<br>id month total<br>1001 1 80<br>1002 1 81<br>1001 2 82<br>1003 1 88<br>1002 2 83<br>1004 3 85<br>.<br>.<br>.<br>.<br>.<br>.<br>想得到这样子：(month1,2,3……代表1月到12月,员工数量不确定即id有很多)<br>id month1 month2 month3&nbsp;&nbsp;&nbsp;4&nbsp;5&nbsp;6&nbsp;7&nbsp;8&nbsp;9&nbsp;10&nbsp;11&nbsp;12<br>1001 80 82 0<br>1002 81 83 <br>1003 88 <br>1004 85<br>.<br>.<br>.<br>.<br>.<br>.<br>这样的SQL语句如何写？

Declare&nbsp;@s&nbsp;nvarchar(1000),@i&nbsp;int<br>select&nbsp;@i=1<br>select&nbsp;@s='select&nbsp;id&nbsp;&nbsp;'<br>while&nbsp;@i&lt;13<br>begin<br>&nbsp;select&nbsp;@s=@s+',max(case&nbsp;month&nbsp;when&nbsp;'+Rtrim(@i)+'&nbsp;then&nbsp;total&nbsp;else&nbsp;null&nbsp;end)&nbsp;as&nbsp;<br>month'+Rtrim(@i)<br>select&nbsp;@i=@i+1<br>end<br>select&nbsp;@s=@s+'&nbsp;from&nbsp;&nbsp;table1&nbsp;group&nbsp;by&nbsp;id&nbsp;'<br>print(@s)<br>exec(@s)<br><br>补充一下：以上是sql2000中执行成功，<br>但在oracle&nbsp;中语句如何写呢？请高手指教！

create&nbsp;table&nbsp;#temp&nbsp;(id&nbsp;int&nbsp;,&nbsp;months&nbsp;int&nbsp;,&nbsp;total&nbsp;int)<br>insert&nbsp;#temp(id,months,total)<br>select&nbsp;1001&nbsp;,&nbsp;1&nbsp;,&nbsp;80<br>insert&nbsp;#temp(id,months,total)<br>select&nbsp;1002&nbsp;,&nbsp;1,&nbsp;&nbsp;81<br>insert&nbsp;#temp(id,months,total)<br>select&nbsp;1001&nbsp;,&nbsp;2&nbsp;,&nbsp;82<br>insert&nbsp;#temp(id,months,total)<br>select&nbsp;1003&nbsp;,&nbsp;1&nbsp;,&nbsp;88<br>insert&nbsp;#temp(id,months,total)<br>select&nbsp;1002&nbsp;,&nbsp;2&nbsp;,&nbsp;83<br>insert&nbsp;#temp(id,months,total)<br>select&nbsp;1004&nbsp;,&nbsp;3&nbsp;,&nbsp;85<br><br>select&nbsp;*&nbsp;from&nbsp;#temp<br><br><br>DECLARE&nbsp;@SQL&nbsp;VARCHAR(8000)&nbsp;<br>&nbsp;set&nbsp;@sql&nbsp;=&nbsp;'select&nbsp;id'&nbsp;<br>&nbsp;select&nbsp;@sql&nbsp;=&nbsp;@sql&nbsp;+&nbsp;',sum(case&nbsp;months&nbsp;when&nbsp;'''+convert(varchar(20),months)+'''&nbsp;then&nbsp;total&nbsp;end)&nbsp;['+convert(varchar(20),months)+']'&nbsp;<br>&nbsp;from&nbsp;(select&nbsp;distinct&nbsp;months&nbsp;from&nbsp;#temp)&nbsp;as&nbsp;a&nbsp;<br>&nbsp;select&nbsp;@sql&nbsp;=&nbsp;@sql+'&nbsp;from&nbsp;#temp&nbsp;group&nbsp;by&nbsp;id'&nbsp;<br>exec(@sql)&nbsp;<br><br><br>drop&nbsp;table&nbsp;#temp

select&nbsp;*&nbsp;from&nbsp;<br>((select&nbsp;distinct&nbsp;ID&nbsp;from&nbsp;yourtable&nbsp;order&nbsp;by&nbsp;ID)&nbsp;a<br>left&nbsp;join&nbsp;(select&nbsp;ID,Total&nbsp;as&nbsp;month1&nbsp;from&nbsp;yourtable&nbsp;where&nbsp;month=1)&nbsp;as&nbsp;m1&nbsp;on&nbsp;a.ID&nbsp;=&nbsp;m1.ID<br>left&nbsp;join&nbsp;(select&nbsp;ID,Total&nbsp;as&nbsp;month1&nbsp;from&nbsp;yourtable&nbsp;where&nbsp;month=2)&nbsp;as&nbsp;m2&nbsp;on&nbsp;a.ID&nbsp;=&nbsp;m2.ID<br>left&nbsp;join&nbsp;(select&nbsp;ID,Total&nbsp;as&nbsp;month1&nbsp;from&nbsp;yourtable&nbsp;where&nbsp;month=3)&nbsp;as&nbsp;m3&nbsp;on&nbsp;a.ID&nbsp;=&nbsp;m3.ID)&nbsp;aa<br><br><br>这样应该能实现

//行转列<br>drop&nbsp;table&nbsp;tt<br>create&nbsp;table&nbsp;tt&nbsp;(<br>&nbsp;&nbsp;id&nbsp;varchar(5),<br>&nbsp;&nbsp;month&nbsp;int,<br>&nbsp;&nbsp;total&nbsp;int)<br><br>insert&nbsp;tt&nbsp;values&nbsp;('0001',1,80)<br><br>insert&nbsp;tt&nbsp;values&nbsp;('0001',2,90)<br><br>insert&nbsp;tt&nbsp;values&nbsp;('0001',3,50)<br><br>insert&nbsp;tt&nbsp;values&nbsp;('0002',1,85)<br><br>insert&nbsp;tt&nbsp;values&nbsp;('0002',3,95)<br><br>insert&nbsp;tt&nbsp;values&nbsp;('0002',5,55)<br><br><br><br>select&nbsp;a.ID,isnull(m1.Month1,0)&nbsp;as&nbsp;Month1,isnull(m2.Month2,0)&nbsp;as&nbsp;Month2,isnull(m3.Month3,0)&nbsp;as&nbsp;Month3,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;isnull(m4.Month4,0)&nbsp;as&nbsp;Month4,isnull(m5.Month5,0)&nbsp;as&nbsp;Month5&nbsp;from&nbsp;<br>(select&nbsp;distinct&nbsp;ID&nbsp;from&nbsp;tt)&nbsp;a<br>left&nbsp;join&nbsp;<br>(select&nbsp;ID,Total&nbsp;as&nbsp;month1&nbsp;from&nbsp;tt&nbsp;where&nbsp;month=1)&nbsp;as&nbsp;m1&nbsp;on&nbsp;a.id&nbsp;=&nbsp;m1.id<br>left&nbsp;join&nbsp;<br>(select&nbsp;ID,Total&nbsp;as&nbsp;month2&nbsp;from&nbsp;tt&nbsp;where&nbsp;month=2)&nbsp;as&nbsp;m2&nbsp;on&nbsp;a.id&nbsp;=&nbsp;m2.id<br>left&nbsp;join&nbsp;<br>(select&nbsp;ID,Total&nbsp;as&nbsp;month3&nbsp;from&nbsp;tt&nbsp;where&nbsp;month=3)&nbsp;as&nbsp;m3&nbsp;on&nbsp;a.id&nbsp;=&nbsp;m3.id<br>left&nbsp;join&nbsp;<br>(select&nbsp;ID,Total&nbsp;as&nbsp;month4&nbsp;from&nbsp;tt&nbsp;where&nbsp;month=4)&nbsp;as&nbsp;m4&nbsp;on&nbsp;a.id&nbsp;=&nbsp;m4.id<br>left&nbsp;join&nbsp;<br>(select&nbsp;ID,Total&nbsp;as&nbsp;month5&nbsp;from&nbsp;tt&nbsp;where&nbsp;month=5)&nbsp;as&nbsp;m5&nbsp;on&nbsp;a.id&nbsp;=&nbsp;m5.id<br><br>经过调试了

其实你要的结果是一个标准的交叉表啊！<br>何必一定要用sql实现？可以用控件实现啊！

收藏了..........经典.........