有一表Test，表中有TurnNo,Qty两个字段，现在我要将行转换为列，即是列不为固定的，该Sql 怎样写？
假如有如下数据:
TurnNo,Qty
T1 100
T2 125
T3 168
转换为
T1,T2,T3
100 125 168
假如有如下数据:
TurnNo,Qty
T1 100
T2 125
T3 168
T5 188
T6 10
转换为
T1,T2,T3,T5,T6
100 125 168 188 10
------解决方案--------------------
declare @sql varchar(8000)
set @sql= ' '
select @[email protected]+ ',max(case TurnNo when ' ' '+TurnNo+ ' ' ' then qty else null end) as [ '+TurnNo+ '] '
from test
group by TurnNo
set @sql=stuff(@sql,1,1, ' ')
exec( 'select '[email protected]+ ' from test ')
------解决方案--------------------
create table test(turnno char(3),qty int )
insert test select 'T1 ', 100
union all select 'T2 ', 125
union all select 'T3 ', 168
union all select 'T5 ', 188
union all select 'T6 ', 10
declare @a varchar(1000)
select @a=isnull(@a+ ', ', ' ')+ ' sum(case when turnno= ' ' '+turnno+ ' ' ' then qty end) [ '+turnno+ '] ' from [test] order by turnno
exec( 'select '[email protected]+ ' from test ')
------解决方案--------------------
declare @sql varchar(8000)
set @sql= 'select '
select @[email protected]+ 'sum(case when TurnNo = ' ' '+TurnNo+ ' ' ' then Qty else 0 end) ' 'TurnNo ' ', '
from t
set @sql=left(@sql,len(@sql)-1)
exec(@sql + 'from t ')
------解决方案--------------------
declare @sql varchar(8000)
set @sql = 'select, '
select @sql = @sql + 'sum(case TurnNo when ' ' '+TurnNo + ' ' '
then Qty else 0 end) as ' ' '+TurnNo+ ' ' ', '
from (select distinct TurnNo from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test '
exec(@sql)
go
------解决方案--------------------
declare @sql varchar(1000)
set @sql= ' '
select @[email protected]+ ',sum(case TurnNo when ' ' '+TurnNo+ ' ' ' then qty else 0 end) as [ '+TurnNo+ '] '
from test
group by TurnNo
set @sql=stuff(@sql,1,1, ' ')
set @sql= 'select '[email protected]+ ' from test '
exec (@sql)
结果：
T1 T2 T3 T5 T6
----------- ----------- ----------- ----------- -----------
100 125 168 188 10