一个动态列转行问题，请高手指教!!!
表t_errect结构如下：
P_name m1 m2 m3
pc1 3 5 8
pc2 6 9 12
要求不使用select ... from ..
union
select ... from ..
......
要得到：
P_name monthly m_value
pc1 m1 3
pc1 m1 5
pc1 m1 8
pc2 m2 6
pc2 m2 9
pc2 m2 12
declare @m_str varchar(50),@monthly int,@sql varchar(200)
set @monthly=1
whlie (@monthly <4)
begin
set @m_str= 'm '+cast(@monthly as varchar(5))
set @sql= 'select P_name, ' ' ' '+@monthly+ ' ' ' ' as monthly, '+@m_str+ ' as m_value from '
+ '(select P_name, '+@m_str+ ' as m_value from t_errect) a '
exec(@sql)
set @monthly=@monthly+1
end
这样做得出的结果分别有3个select结果，如何将这些结果都集中在一个select中显示出来？
[解决办法]
Create Table t_errect
(P_nameVarchar(10),
m1Int,
m2Int,
m3Int)
Insert t_errect Select 'pc1 ', 3, 5, 8
Union All Select 'pc2 ', 6, 9, 12
GO
declare @m_str varchar(50),@monthly int,@sql varchar(2000)
select @monthly=1, @sql = ' '
while (@monthly <4)
begin
set @m_str= 'm '+cast(@monthly as varchar(5))
set @sql= @sql + ' union all select P_name, ' ' '+ @m_str+ ' ' ' as monthly, '+@m_str+ ' as m_value from t_errect '
set @monthly=@monthly+1
end
select @sql = Stuff(@sql, 1, 10, ' ')
exec(@sql)
GO
Drop Table t_errect
--Result
/*
P_namemonthlym_value
pc1m13
pc2m16
pc1m25
pc2m29
pc1m38
pc2m312
*/
