34,576
社区成员
发帖
与我相关
我的任务
分享
select top 0* into #last from #Result
select top 0* into #update from #Result
declare @i int,@last int
set @i=1
select @last=MAX(LEN(item)) from #Result
while @i<@last
begin
insert into #last
select * from #Result where LEN(Item)=2
delete #Result where LEN(Item)=2
insert into #last
select sequence,substring(item,1,CHARINDEX('、',item)-1),MinValue,MaxValue from #Result
insert into #update
select sequence,SUBSTRING(item,CHARINDEX('、',item)+1,LEN(item)),MinValue,MaxValue from #Result
truncate table #result
insert into #Result
select * from #update
truncate table #update
select @i=@i+3
end
select Sequence,Item,MinValue+'_'+MaxValue as value into #last_laset From #last
select * From #last_laset pivot(max(value) for item in(上等,中等,下等)) a
结果:
select top 0* into #last from #Result
select top 0* into #update from #Result
declare @i int,@last int
set @i=1
select @last=MAX(LEN(item)) from #Result
while @i<@last
begin
insert into #last
select * from #Result where LEN(Item)=2
delete #Result where LEN(Item)=2
insert into #last
select sequence,substring(item,1,CHARINDEX('、',item)-1),MinValue,MaxValue from #Result
insert into #update
select sequence,SUBSTRING(item,CHARINDEX('、',item)+1,LEN(item)),MinValue,MaxValue from #Result
truncate table #result
insert into #Result
select * from #update
truncate table #update
select @i=@i+3
end
select a.Sequence,b.MinValue+'_'+b.MaxValue as '上等' into #linshi1 from #last a inner join #last b
on a.Sequence=b.Sequence and a.Item=b.Item where a.Item='上等'
select a.Sequence,b.MinValue+'_'+b.MaxValue as '中等' into #linshi2 from #last a inner join #last b
on a.Sequence=b.Sequence and a.Item=b.Item where a.Item='中等'
select a.Sequence,b.MinValue+'_'+b.MaxValue as '下等' into #linshi3 from #last a inner join #last b
on a.Sequence=b.Sequence and a.Item=b.Item where a.Item='下等'
select a.*,b.[中等],c.[下等] from #linshi1 a,#linshi2 b,#linshi3 c
where a.Sequence=b.Sequence and a.Sequence=c.Sequence
结果如下:
CREATE TABLE #Result
(
Sequence INT,
Item NVARCHAR(100),
MinValue NVARCHAR(100),
MaxValue NVARCHAR(100)
)
INSERT INTO #Result
SELECT 1,N'上等、中等、下等','16.0%','18.0%'
UNION ALL
SELECT 2,N'上等、中等','16.0%','18.0%'
UNION ALL
SELECT 2,N'下等','17.0%','19.0%'
;with cte as
(
SELECT SUBSTRING(Item,number,CHARINDEX('、',Item+'、',number)-number) as Item
,MinValue,MaxValue
from #Result a, master..spt_values
where number >=1 and type='p'
and number<len(Item) and substring('、'+Item,number,1)='、'
)
select row_id,[上等]=max(case when Item='上等' then MinValue+' - '+MaxValue end)
,[中等]=max(case when Item='中等' then MinValue+' - '+MaxValue end)
,[下等]=max(case when Item='下等' then MinValue+' - '+MaxValue end)
from
(
select *,row_id=row_number() over(partition by Item order by getdate()) from cte
)t
group by row_id
drop table #Result
/*
row_id 上等 中等 下等
1 16.0% - 18.0% 16.0% - 18.0% 16.0% - 18.0%
2 16.0% - 18.0% 16.0% - 18.0% 17.0% - 19.0%
*/
--CREATE TABLE #Result
--(
--Sequence INT,
--Item VARCHAR(100),
--MinValue VARCHAR(100),
--MaxValue VARCHAR(100)
--)
--INSERT INTO #Result
--SELECT 1,'上等、中等、下等'、'16.0%'、'18.0%'
--UNION ALL
--SELECT 2,'上等、中等'、'16.0%'、'18.0%'
--UNION ALL
--SELECT 2,'下等'、'17.0%'、'19.0%'
IF OBJECT_ID('tempdb..#t','u') IS NOT NULL
DROP TABLE #t
select
Sequence,
a.MinValue+'-'+ a.MaxValue Value,
SUBSTRING(Item,number,CHARINDEX('、',Item+'、',number)-number) as Item INTO #t
from
#Result a,master..spt_values
where
number >=1 and number<=len(Item)
and type='p'
and substring('、'+Item,number,1)='、'
select Sequence,MAX(CASE WHEN item='上等' THEN value ELSE NULL END )'上等',
MAX(CASE WHEN item='中等' THEN value ELSE NULL END )'中等',
MAX(CASE WHEN item='下等' THEN value ELSE NULL END )'下等'
from #t
GROUP BY Sequence
--declare @s nvarchar(4000)
--set @s=''
--Select @s=@s+','+quotename(item)+'=max(case when [item]='+quotename(item,'''')+' then [Value] else null end)'
--from #t group by item
--PRINT @s
--exec('select [Sequence]'+@s+' from #t group by [Sequence]')
/*
Sequence 上等 中等 下等
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 16.0%-18.0% 16.0%-18.0% 16.0%-18.0%
2 16.0%-18.0% 16.0%-18.0% 17.0%-19.0%
*/
--CREATE TABLE #Result
--(
--Sequence INT,
--Item VARCHAR(100),
--MinValue VARCHAR(100),
--MaxValue VARCHAR(100)
--)
--INSERT INTO #Result
--SELECT 1,'上等、中等、下等'、'16.0%'、'18.0%'
--UNION ALL
--SELECT 2,'上等、中等'、'16.0%'、'18.0%'
--UNION ALL
--SELECT 2,'下等'、'17.0%'、'19.0%'
IF OBJECT_ID('tempdb..#t','u') IS NOT NULL
DROP TABLE #t
select
Sequence,
a.MinValue+'-'+ a.MaxValue Value,
SUBSTRING(Item,number,CHARINDEX('、',Item+'、',number)-number) as Item INTO #t
from
#Result a,master..spt_values
where
number >=1 and number<=len(Item)
and type='p'
and substring('、'+Item,number,1)='、'
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(item)+'=max(case when [item]='+quotename(item,'''')+' then [Value] else null end)'
from #t group by item
exec('select [Sequence]'+@s+' from #t group by [Sequence]')
/*
Sequence 上等 下等 中等
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 16.0%-18.0% 16.0%-18.0% 16.0%-18.0%
2 16.0%-18.0% 17.0%-19.0% 16.0%-18.0%
*/