sql语句行转列问题，比较难，求解决方法，高手进

Question

--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%*/

Answer 1

如果只有三等，直接写死--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%*/

Answer 2

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%
*/

Answer 3

楼上的都是大神，估计我这个方法最笨了：
select top 0* into #last from #Result
select top 0* into #update from #Result
declare @i int,@last int
set @i=1select @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
endselect 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结果如下：

Answer 4

楼主，给你更新了一个行列转换的算法：如下：select top 0* into #last from #Result
select top 0* into #update from #Result
declare @i int,@last int
set @i=1select @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结果：