sqlserver的行列转换问题
目前表结构如下：
ROW_INFO_ID NAME NE_ID VALUE1 VALUE2 VALUE3 NAME_EN
4096 CELL 4339 0 0 0 BCCH
4096 CELL 4339 0 0 0 BCCH
4096 CELL 4339 1 2 3 BCCH
4096 CELL 4339 0 0 0 BSIC
4096 CELL 4339 0 0 0 BSIC
4096 CELL 4339 1 2 3 BSIC
4096 CELL 4339 0 0 0 CI
4096 CELL 4339 0 0 0 CI
4096 CELL 4339 1 2 3 CI
现在想通过行列转换转换为
NAME BCCH BSIC CI
CELL 0 0 0
CELL 0 0 0
CELL 1 2 3
请问该怎么写sql语句，我自己写的出现很多null SQL?Server
[解决办法]
create table mn
(ROW_INFO_ID int, NAME varchar(10), NE_ID int,
VALUE1 int, VALUE2 int, VALUE3 int, NAME_EN varchar(10))
insert into mn
select 4096, 'CELL', 4339, 0, 0, 0, 'BCCH' union all
select 4096, 'CELL', 4339, 0, 0, 0, 'BCCH' union all
select 4096, 'CELL', 4339, 1, 2, 3, 'BCCH' union all
select 4096, 'CELL', 4339, 0, 0, 0, 'BSIC' union all
select 4096, 'CELL', 4339, 0, 0, 0, 'BSIC' union all
select 4096, 'CELL', 4339, 1, 2, 3, 'BSIC' union all
select 4096, 'CELL', 4339, 0, 0, 0, 'CI' union all
select 4096, 'CELL', 4339, 0, 0, 0, 'CI' union all
select 4096, 'CELL', 4339, 1, 2, 3, 'CI'
select NAME,[BCCH],[BSIC],[CI]
from (
select NAME,VALUE,NAME_EN,
row_number() over(partition by NAME_EN order by getdate()) 'rn'
from
(select NAME,VALUE1 'VALUE',NAME_EN from mn where NAME_EN='BCCH'
union all
select NAME,VALUE2 'VALUE',NAME_EN from mn where NAME_EN='BSIC'
union all
select NAME,VALUE3 'VALUE',NAME_EN from mn where NAME_EN='CI'
) t) y
pivot(max(VALUE) for NAME_EN in([BCCH],[BSIC],[CI])) p
/*
NAME BCCH BSIC CI
---------- ----------- ----------- -----------
CELL 0 0 0
CELL 0 0 0
CELL 1 2 3
(3 row(s) affected)
*/
