请问一个SQL查询语句，使用一条命令将纵向结果转成横向结果显示

请教一个SQL查询语句，使用一条命令将纵向结果转成横向结果显示
运行环境：WindowsXP(SP3)+Sql2000

请教一个SQL查询语句，使用一条命令将纵向结果转成横向结果显示，代码如下：

SQL code

create table #tmp(style nvarchar(10),gx_type nvarchar(2),total_price decimal(10,4))insert into #tmp values('10168L','C',0.6541)insert into #tmp values('10168L','G',0.6542)insert into #tmp values('10168L','M',0.6543)insert into #tmp values('10168N','C',0.6544)insert into #tmp values('10168M','G',0.6545)insert into #tmp values('10168M','M',0.6546)select * from #tmpcreate table #tmq(mac_code nvarchar(10),mac_name nvarchar(20))insert into #tmq values('C','代码C')insert into #tmq values('G','代码G')insert into #tmq values('M','代码M')select * from #tmqdrop table #tmpdrop table #tmq---------如何用一条命令实现以下结果-------------/*名称        代码C        代码G        代码M1016L        .6541        .6542        .654310168N        .6544        0        010168M        0        .6545        .6546*/

SQL code

create table #tmp(style nvarchar(10),gx_type nvarchar(2),total_price decimal(10,4))insert into #tmp values('10168L','C',0.6541)insert into #tmp values('10168L','G',0.6542)insert into #tmp values('10168L','M',0.6543)insert into #tmp values('10168N','C',0.6544)insert into #tmp values('10168M','G',0.6545)insert into #tmp values('10168M','M',0.6546)--select * from #tmpcreate table #tmq(mac_code nvarchar(10),mac_name nvarchar(20))insert into #tmq values('C','代码C')insert into #tmq values('G','代码G')insert into #tmq values('M','代码M')--select * from #tmqselect  a.style,  MAX(case gx_type when 'c' then total_price else 0.0 end) as 代码C,  MAX(case gx_type when 'g' then total_price else 0.0 end) as 代码G,  MAX(case gx_type when 'm' then total_price else 0.0 end) as 代码Mfrom  #tmp a join #tmq bon  a.gx_type=b.mac_codegroup  by  a.styledrop table #tmpdrop table #tmq/*style      代码C                                     代码G                                     代码M---------- --------------------------------------- --------------------------------------- ---------------------------------------10168L     0.6541                                  0.6542                                  0.654310168M     0.0000                                  0.6545                                  0.654610168N     0.6544                                  0.0000                                  0.0000(3 行受影响)*/
[解决办法]
SQL code
select style,        代码C=max(case when mac_name='代码C' then total_price else 0 end),       代码G=max(case when mac_name='代码G' then total_price else 0 end),       代码M=max(case when mac_name='代码M' then total_price else 0 end)from #tmp a inner join #tmq b on a.gx_type=b.mac_code group by a.style/*style      代码C                                     代码G                                     代码M---------- --------------------------------------- --------------------------------------- ---------------------------------------10168L     0.6541                                  0.6542                                  0.654310168M     0.0000                                  0.6545                                  0.654610168N     0.6544                                  0.0000                                  0.0000(3 行受影响)*/
[解决办法]
SQL code
create table #tmp(style nvarchar(10),gx_type nvarchar(2),total_price decimal(10,4))insert into #tmp values('10168L','C',0.6541)insert into #tmp values('10168L','G',0.6542)insert into #tmp values('10168L','M',0.6543)insert into #tmp values('10168N','C',0.6544)insert into #tmp values('10168M','G',0.6545)insert into #tmp values('10168M','M',0.6546)select * from #tmpcreate table #tmq(mac_code nvarchar(10),mac_name nvarchar(20))insert into #tmq values('C','代码C')insert into #tmq values('G','代码G')insert into #tmq values('M','代码M')select * from #tmqselect style,[代码C],[代码G],[代码M] from (select a.style ,b.mac_name,a.total_price from #tmp a left join #tmq b on a.gx_type=b.mac_code ) apivot (max(a.total_price) for mac_name in ([代码C],[代码G],[代码M])) pvt 
 
[解决办法]
都是有穷列举啊，#tmq表数据不知道多少条的时候怎么办呢。
[解决办法]
SQL code
create table #tmp(style nvarchar(10),gx_type nvarchar(2),total_price decimal(10,4))insert into #tmp values('10168L','C',0.6541)insert into #tmp values('10168L','G',0.6542)insert into #tmp values('10168L','M',0.6543)insert into #tmp values('10168N','C',0.6544)insert into #tmp values('10168M','G',0.6545)insert into #tmp values('10168M','M',0.6546)create table #tmq(mac_code nvarchar(10),mac_name nvarchar(20))insert into #tmq values('C',N'代码C')insert into #tmq values('G',N'代码G')insert into #tmq values('M',N'代码M')declare @s nvarchar(4000)set @s=''Select     @s=@s+N','+quotename(mac_name)+N'=max(case when gx_type=N'+quotename(mac_code,'''')+N' then total_price else 0  end)'from #tmq --顯示生成語句print N'select style as 名称'+@s+N' from #tmp group by style'exec(N'select style as 名称'+@s+N' from #tmp group by style')go/*名称    代码C    代码G    代码M10168L    0.6541    0.6542    0.654310168M    0.0000    0.6545    0.654610168N    0.6544    0.0000    0.0000*/drop table #tmpdrop table #tmq