当前位置：首页 → 问答吧 → 请教一个SQL查询语句，使用一条命令将纵向结果转成横向结果显示

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

时间：2011-12-02

来源：互联网

运行环境：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 #tmp

create 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 #tmq
drop table #tmp
drop table #tmq
---------如何用一条命令实现以下结果-------------
/*
名称        代码C        代码G        代码M
1016L        .6541        .6542        .6543
10168N        .6544        0        0
10168M        0        .6545        .6546
*/

作者: cnjack 发布时间: 2011-12-02

转置,参考
http://blog.csdn.net/qianjin036a/article/details/6582237

作者: qianjin036a 发布时间: 2011-12-02

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 #tmp

create 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 #tmq

select
  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 代码M
from
  #tmp a join #tmq b
on
  a.gx_type=b.mac_code
group  by
  a.style
drop table #tmp
drop table #tmq

/*style      代码C                                     代码G                                     代码M
---------- --------------------------------------- --------------------------------------- ---------------------------------------
10168L     0.6541                                  0.6542                                  0.6543
10168M     0.0000                                  0.6545                                  0.6546
10168N     0.6544                                  0.0000                                  0.0000

(3 行受影响)
*/

作者: fredrickhu 发布时间: 2011-12-02

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.6543
10168M     0.0000                                  0.6545                                  0.6546
10168N     0.6544                                  0.0000                                  0.0000

(3 行受影响)
*/

作者: pengxuan 发布时间: 2011-12-02

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 #tmp

create 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 #tmq

select 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 ) a
pivot (max(a.total_price) for mac_name in ([代码C],[代码G],[代码M])) pvt