*数据表怎么换化显示有关问题*

***数据表如何换化显示问题***
原如表结果
 ID TYPE length
5309622DT30.00
8002619BR15.00
1002619CO30.00
1002619BR30.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.00insert into tb select '8002619','BR',15.00insert into tb select '1002619','CO',30.00insert into tb select '1002619','BR',30.00go;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    CA1001                    CO         30.001                    BR         30.002                    5309622    CA1002                    DT         30.003                    BR         15.003                    8002619    CA100(7 行受影响)*/godrop table tb