***数据表如何换化显示问题***
原如表结果
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