ＳＱＬ行转列问题(10分)

表t结构如下：<br>t(matname,prodname,sincost,consume)<br>其数据如下：<br>//----------------------------------<br>matname &nbsp; prodname &nbsp; &nbsp; &nbsp;sincost &nbsp; consume<br>A1 &nbsp; &nbsp; &nbsp; &nbsp;B1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.02 &nbsp; &nbsp; &nbsp;0.3<br>A1 &nbsp; &nbsp; &nbsp; &nbsp;B2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.09 &nbsp; &nbsp; &nbsp;0.012<br>A1 &nbsp; &nbsp; &nbsp; &nbsp;B3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.012 &nbsp; &nbsp; 0.65<br>A1 &nbsp; &nbsp; &nbsp; &nbsp;B4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.034 &nbsp; &nbsp; 1.25<br>A2 &nbsp; &nbsp; &nbsp; &nbsp;B1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.26 &nbsp; &nbsp; &nbsp;2.65<br>A2 &nbsp; &nbsp; &nbsp; &nbsp;B2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.025 &nbsp; &nbsp; 2.65<br>A2 &nbsp; &nbsp; &nbsp; &nbsp;B3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.54 &nbsp; &nbsp; &nbsp;5.69<br>A2 &nbsp; &nbsp; &nbsp; &nbsp;B4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.025 &nbsp; &nbsp; 1.54<br>//------------------------------------<br>要将其转化成以下结构：<br>表头如下：<br>matname　 &nbsp; &nbsp; &nbsp; &nbsp;B1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B4<br> &nbsp; &nbsp; &nbsp; &nbsp; sincost &nbsp; consume &nbsp; sincost &nbsp;consume &nbsp; sincost consume &nbsp; sincost consume<br>//------------------------------------------------------------------------------<br>A1 &nbsp; &nbsp; &nbsp; 0.02 &nbsp; &nbsp; &nbsp;0.3 &nbsp; &nbsp; &nbsp;0.09 &nbsp; &nbsp; &nbsp;0.012 &nbsp; &nbsp; 0.012 &nbsp; 0.65 &nbsp; &nbsp; &nbsp;0.034 &nbsp; 1.25<br>A2 &nbsp; &nbsp; &nbsp; 0.26 &nbsp; &nbsp; &nbsp;2.65 &nbsp; &nbsp; 0.025 &nbsp; &nbsp; 2.65 &nbsp; &nbsp; &nbsp;0.54 &nbsp; &nbsp;5.69 &nbsp; &nbsp; &nbsp;0.025 &nbsp; 1.54

可以用临时表的方式，我曾经写过一个类似的问题`

--创建临时表结构<br>if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tmp_TTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)<br>drop table [dbo].[Tmp_TTable]<br>GO<br><br>create table Tmp_TTable<br>(<br>TID varchar(4),<br>TName varchar(10),<br>TDate varchar(10)<br>)<br>GO<br><br>--修改临时表结构<br>declare @MaxNUM int,<br> &nbsp; &nbsp; &nbsp; &nbsp;@NUM int,<br> &nbsp; &nbsp; &nbsp; &nbsp;@SQL varchar(255)<br>select @NUM=1 <br>select @MaxNUM=max(NUM) from (select count(*) as NUM from TTable group by TID,TDate) as A<br><br>while @NUM&lt;=@MaxNUM<br>begin<br> &nbsp;select @SQL='alter table Tmp_TTable add TTime'+ cast(@NUM as varchar)+ ' varchar(10)'<br> &nbsp;exec(@SQL)<br> &nbsp;select @NUM=@NUM+ 1<br>end<br><br>--插入数据值<br>declare @TID varchar(4),<br> &nbsp; &nbsp; &nbsp; &nbsp;@TName varchar(10),<br> &nbsp; &nbsp; &nbsp; &nbsp;@TDate varchar(10),<br> &nbsp; &nbsp; &nbsp; &nbsp;@TTime varchar(10)<br><br>insert into Tmp_TTable(TID,TName,TDate) select distinct TID,TName,TDate from TTable<br><br>declare MyCurOne scroll cursor for <br> &nbsp;select TID,TName,TDate from TTable order by TID,TDate<br>open MyCurOne <br><br>fetch next from MyCurOne into @TID,@TName,@TDate<br><br>while @@FETCH_STATUS=0<br>begin<br> &nbsp;declare MyCurTwo cursor for<br> &nbsp; &nbsp;select TTime from TTable where TID=@TID and TName=@TName and TDate=@TDate<br> &nbsp;open MyCurTwo<br> &nbsp; &nbsp; select @NUM=1<br> &nbsp; &nbsp; fetch next from MyCurTwo into @TTime<br> &nbsp; &nbsp; while @@FETCH_STATUS=0<br> &nbsp; &nbsp; begin<br> &nbsp; &nbsp; &nbsp; select @SQL='update Tmp_TTable set TTime'+ cast(@NUM as varchar)+ '='''+ @TTime+ ''' where TID='''+ @TID+ ''' and TName='''+ @TName+ ''' and TDate='''+ @TDate+ ''''<br> &nbsp; &nbsp; &nbsp; exec(@SQL)<br> &nbsp; &nbsp; &nbsp; fetch next from MyCurTwo into @TTime<br> &nbsp; &nbsp; &nbsp; select @NUM=@NUM+1<br> &nbsp; &nbsp; end<br> &nbsp;close MyCurTwo<br> &nbsp;deallocate MyCurTwo<br><br> &nbsp;fetch relative @NUM from MyCurOne into @TID,@TName,@TDate<br>end<br><br>close MyCurOne<br>deallocate MyCurOne<br>GO<br><br>方法很苯，你可以参照修改一下，不知道对你有没有帮助~~

case ,<br>select <br>(case column_a when a &nbsp; xxx else xxx end) as a;<br>(case column_a when b &nbsp; xxx else xxx end) as b;<br>.<br>.<br>.<br>from table <br><br><br>或者是采用 一堆联表 <br>select <br><br>from table<br>inner join <br>(select &nbsp;from table where table.column='b') b on table.xx=b.xx<br>inner join<br>(select &nbsp;from table where table.column='c') c on table.xx=c.xx<br><br>这两种我都做过,也都实现过把数据直接转换成列,总的来说还是case 最灵活了.<br><br>字段少,固定数据的情况的下 可以这么做做, 字段数据动态的话,最好还是存储过程了.