日期:2014-05-18  浏览次数:20381 次

***数据表如何换化显示问题***
原如表结果
 ID TYPE length
5309622 DT 30.00
8002619 BR 15.00
1002619 CO 30.00
1002619 BR 30.00


需要转化成,列名无所谓


 5309622 CA100 -----(固定值)
  DT 30.00
 8002619 CA100
  BR 15.00
 1002619 CA100 -----有2条相同ID,应该显示如下
  CO 30.00
  BR 30.00

------解决方案--------------------
SQL code
create table tb(ID varchar(10),TYPE varchar(10),length decimal(10,2))
insert into tb select '5309622','DT',30.00
insert into tb select '8002619','BR',15.00
insert into tb select '1002619','CO',30.00
insert into tb select '1002619','BR',30.00
go
;with c1 as(
select row_number()over(order by (select 1))rn,id,'CA100' as col from (select distinct id from tb)t
),c2 as(
select a.rn,b.type,convert(varchar(10),b.length)col from c1 a inner join tb b on a.id=b.id
)
select * from c1 union all select * from c2 order by rn
/*
-------------------- ---------- ----------
1                    1002619    CA100
1                    CO         30.00
1                    BR         30.00
2                    5309622    CA100
2                    DT         30.00
3                    BR         15.00
3                    8002619    CA100

(7 行受影响)

*/
go
drop table tb