静态列表列转行
create table feeinfo(feeid int ,telephone varchar(10),fname varchar(10))
go
insert into feeinfo values(1,'7135000','月租费')
insert into feeinfo values(2,'7135000','市话费')
insert into feeinfo values(3,'7135000','长话费')
go
select *from feeinfo
go
create table userinfo(userid int,fee int)
insert into userinfo values(1,20)
insert into userinfo values(2,30)
insert into userinfo values(3,40)
go
select *from userinfo
--我想显示为如下格式:电话号码 月租费 市话费 长话费
-- 7135000 20 20 20
select t.telephone as 电话号码,
max(case t.fname when '月租费' then u.fee else 0 end) as '月租费',
max(case t.fname when '市话费' then u.fee else 0 end) as '市话费',
max(case t.fname when '长话费' then u.fee else 0 end) as '长话费'
from feeinfo t inner join userinfo u on t.feeid=u.userid
group by t.telephone
动态列表列转行
create table carinfo(company varchar(20) ,cartype int,dangan varchar(20),carno varchar(10),carname varchar(20))
go
insert into carinfo values('赛捷',1,'sj-1','湘111','小蓝')
insert into carinfo values('赛捷',2,'sj-2','湘222','小红')
insert into carinfo values('赫迪',1,'sj-3','湘333','小方')
insert into carinfo values('赛捷',2,'sj-4','湘444','小北')
go
create table feeinfo(feeid int ,carno varchar(20),feetime varchar(10),feeno varchar(10))
go
insert into feeinfo values(1,'湘111','2004-10-11','0001')
insert into feeinfo values(2,'湘111','2005-10-11','0002')
insert into feeinfo values(3,'湘111','2006-10-11','0003')
insert into feeinfo values(4,'湘222','2003-10-11','0004')
go
create table feetype(feetypeid int ,cartype int,feename varchar(32),feeprice int)
go
insert into feetype values(1,1,'过路费',20)
insert into feetype values(2,1,'刷卡费',30)
insert into feetype values(3,1,'保险费',40)
insert into feetype values(4,1,'营运费',20)
go
select *from carinfo
select *from feeinfo
select *from feetype
declare @sql1 varchar(8000)
set @sql1 = 'select f.feetime as 日期,f.feeno as 单据 , c.company as 公司, c.dangan as 档案编号, f.carno as 车牌 ,c.carname as 车主'
select @sql1 = @sql1 + ' , max(case t.feename when ''' + t.feename + ''' then t.feeprice else null end) [' + t.feename + ']'
from (select distinct feename from feetype)as t
set @sql1 = @sql1 + ' from (select feename ,feeprice,cartype from feetype)t inner join (select company,cartype,dangan,carno,carname from carinfo) c on t.cartype=c.cartype inner join (select carno,feetime,feeno from feeinfo) f on f.carno=c.carno group by f.feeno,f.feetime,c.company,c.dangan ,f.carno,c.carname '
exec(@sql1)