mssql根据条件数据列分解成多行数据,急
发布网友
发布时间:2022-04-15 01:23
我来回答
共1个回答
热心网友
时间:2022-04-15 02:52
创建测试表
create table t
(id int,
name varchar(10),
type varchar(10))
insert into t values (1,'苹果','003')
insert into t values (2,'香蕉','A04')
insert into t values (3,'西瓜',NULL)
insert into t values (4,'石榴','002')
运行
select t.*,t.name+s.name2 name2 from t left join
(select distinct type,
case when isnumeric(replace(type,0,''))=0 then
left(replace(type,0,''),1)+cast(t1.id as varchar)+'级' else cast(t1.id as varchar)+'级' end name2
from t,
(select 1 id union all
select 2 id union all
select 3 id union all
select 4 id ) t1
where type is not null
and t1.id<=right(replace(type,0,''),1)) s
on t.type=s.type
结果
你可以把这部分单独拿出来运行一下,看下结果
select distinct type,
case when isnumeric(replace(type,0,''))=0 then
left(replace(type,0,''),1)+cast(t1.id as varchar)+'级' else cast(t1.id as varchar)+'级' end name2
from t,
(select 1 id union all
select 2 id union all
select 3 id union all
select 4 id ) t1
where type is not null
and t1.id<=right(replace(type,0,''),1)