sql交叉问题500分(0分)

表DEP <br>GONGX_NO(工序编号） DEP_NAME（工序名称）<br>1 钻孔<br>2 镀铜<br>3 曝光<br>表EMP <br>DINGD_NO（订单编号） EMP_QTY(订单数量) EMP_DATE(下单日期)<br>ds1080 1110000 2005-1-1<br>ds222 200000 2005-2-2<br>ds1080 13000 2005-1-2<br>表EHW <br>DINGD_NO（订单编号） GONGX_NO（工序编号） EHW_QTY(生产数量) EHW_DATE(生产日期)<br>ds1080 1 555 2005-1-1<br>ds1080 2 816 2005-1-1<br>ds1080 3 223 2005-2-2<br>ds222 1 25 2005-1-2<br>ds222 3 66 2005-1-2<br>得到以下表<br>DINGD_NO（订单编号） 钻孔 镀铜 曝光 EHW_DATE生产日期<br>ds1080 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;555 &nbsp; &nbsp; &nbsp; 816 &nbsp; &nbsp; &nbsp; &nbsp; 0 2005-1-1<br>ds222 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 25 &nbsp;0 66 2005-1-2<br>ds1080 &nbsp; &nbsp; &nbsp; &nbsp; 0 0 233 2005-2-2<br>以上给500分

select &nbsp;distint &nbsp; GONGX_NO as &nbsp;'GONGX_NO' &nbsp;into #tmp from Dep <br>&nbsp; &nbsp;<br><br>&nbsp; <br>select &nbsp;a.DINGD_NO, b.EHW_DATE ,DEP_NAME,EHW_QTY<br>into #tmp2<br>from emp a <br>inner join EHW b &nbsp;on a.DINGD_NO = b.DINGD_NO <br>inner join &nbsp;DEP c on b.GONGX_NO = c.GONGX_NO<br><br>declare @sql string<br><br>set @sql= 'select &nbsp; DINGD_NO ,EHW_DATE ' <br>&nbsp; &nbsp; &nbsp; <br>set @sql = @sql + ' max(case DEP_NAME when '''+DEP_NAME+''' then &nbsp;EHW_QTY else 0 end) as DEP_NAME , ' &nbsp;from &nbsp; &nbsp;#tmp<br><br>set @sql = substring(@sql,1,len(@sql)-1) + 'from #tmp2 group by &nbsp;DINGD_NO ,EHW_DATE'

用sql的交叉表就可以了,这是PCB线路板公司中常用的横向WIP数据的显示方式.[]