***数据表如何换化显示问题***

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

乱了。

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

一行显示 ID和CA100
换一行显示 TYPE，LENTH， ID相同时，只显示一次ID行

SQL code

select
   case px when 1 then id else '' end as ID ,
   case px when 1 then TYPE else '' end as TYPE,length
from
   (select px=row_number()over(partition by id order by getdate()),* from tb)t

他要的好像不是这个。弄不明白他

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

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,col desc
/*
rn                   id         col
-------------------- ---------- ----------
1                    1002619    CA100
1                    CO         30.00
1                    BR         30.00
2                    5309622    CA100
2                    DT         30.00
3                    8002619    CA100
3                    BR         15.00

(7 行受影响)
*/
go
drop table tb

+char(10)+ 用这个吧 转列 字符形式显示

SQL code

select id,'ca001'+char(10)+[type],length from tb

select type,length
from 
(
select id,type,length,0 as orderid from tablename
union all
select distinct id,id,null,1 as orderid from tablename
)a order by id,orderid desc

select type,length
from 
(
select id,type,length,0 as orderid from tablename
union all
select distinct id,id,'固定值',1 as orderid from tablename
)a order by id,orderid desc