简单的行列装换
有一个行数不确定的表table1:
ID Name
1 FFF
2 CCC
3 AAA
4 DDD
5 BBB
其中“Name”列没有重复值，
还有一个table2
Name price Year
CCC 150 2006
AAA 130 2006
DDD 160 2006
BBB 180 2006
FFF 100 2007
CCC 150 2007
AAA 130 2007
DDD 160 2007
......
......
现在需要将table2转换成table3，如下表：
Year FF CCC AAA DDD BBB
2006 0 150 130 160 180
2007 100 150 130 160 0
转化要求：
table3的行顺序要求按照table1的ID字段的排序
[解决办法]
declare sql varchar(8000);
set sql= ' '
select sql=sql+ ',sum(case name when ' ' '+name+ ' ' ' then price else 0 end) as ' ' '+name+ ' ' ' ' from table1 order by id
set sql= 'select year '+sql+ ' from table2 group by year '
exec(sql)
[解决办法]
declare @sql varchar(8000)
select @sql = 'select year '
select @sql = @sql + ',case when name = ' ' ' +name + ' ' ' then price else 0 end as ' ' '+ name ' '
from table1
select @sql =@sql + ' from table2 group by Year '
--print @sql
exec (@sql)
[解决办法]
有一个行数不确定的表table1:
ID Name
1 FFF
2 CCC
3 AAA
4 DDD
5 BBB
其中“Name”列没有重复值，
还有一个table2
Name price Year
CCC 150 2006
AAA 130 2006
DDD 160 2006
BBB 180 2006
FFF 100 2007
CCC 150 2007
AAA 130 2007
DDD 160 2007
......
......
现在需要将table2转换成table3，如下表：
Year FF CCC AAA DDD BBB
2006 0 150 130 160 180
2007 100 150 130 160 0
转化要求：
table3的行顺序要求按照table1的ID字段的排序
create table ta(id int,name varchar(10))
insert ta select 1, 'FFF '
insert ta select 2, 'CCC '
insert ta select 3, 'AAA '
insert ta select 4, 'DDD '
insert ta select 5, 'BBB '
create table tb( Name varchar(10), price int,[Year] int)
insert tb select 'CCC ', 150, 2006
insert tb select 'AAA ', 130, 2006
insert tb select 'DDD ', 160, 2006
insert tb select 'BBB ', 180, 2006
insert tb select 'FFF ', 100, 2007
insert tb select 'CCC ', 150, 2007
insert tb select 'AAA ', 130, 2007
insert tb select 'DDD ', 160, 2007
declare @sql varchar(4000)
set @sql= ' '
select @sql=@sql+ ',[ '+name+ ']=sum(case name when '+quotename(name, ' ' ' ')+ ' then price else 0 end) '
from ta group by [name] order by name desc
set @sql= 'select [year] '+@sql+ ' from tb group by [year] order by [year] '
exec(@sql)
year FFF DDD CCC BBB AAA
----------- ----------- ----------- ----------- ----------- -----------
2006 0 160 150 180 130
2007 100 160 150 0 130
[解决办法]
环境：
create table #t1
(ID int,
Name varchar(50)
)
insert into #t1
select '1 ', 'FFF ' union all select '2 ', 'CCC ' union all select '3 ', 'AAA ' union all select '4 ', 'DDD ' union all select '5 ', 'BBB '
select * from #t1
create table #t2
(
Name varchar(50),
price int,
[Year] varchar(4)
)
insert into #t2
select 'CCC ', '150 ', '2006 ' union all select 'AAA ', '130 ', '2006 ' union all select 'DDD ', '160 ', '2006 ' union all select 'BBB ', '180 ', '2006 ' union all select 'FFF ', '100 ', '2007 ' union all select 'CCC ', '150 ', '2007 ' union all select 'AAA ', '130 ', '2007 ' union all select 'DDD ', '160 ', '2007 '
select * from #t2
动态sql语句：
declare @sql varchar(8000);
set @sql= ' '
select @sql=@sql+ ',sum(case name when ' ' '+name+ ' ' ' then price else 0 end) as ' ' '+name+ ' ' ' '
from #t1 order by id
set @sql= 'select year '+@sql+ ' from #t2 group by year '
exec(@sql)
结果：
----------
Year FF CCC AAA DDD BBB
2006 0 150 130 160 180
2007 100 150 130 160 0
