原如表结果
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.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