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%'
SELECT * FROM #Result
-- 结果集
--Sequence Item MinValue MaxValue --这一列是字段名
--1 上等、中等、下等 16.0% 18.0%
--2 上等、中等 16.0% 18.0%
--2 下等 17.0% 19.0%
--现在需要变成如下格式：
-- Sequence 上等 中等 下等 --原来的字段Item中的值上等、中等、下等变成了三列
-- 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%
求解决方法
--SELECT 2,'下等'、'17.0%'、'19.0%' IF OBJECT_ID('tempdb..#t','u') IS NOT NULLDROP 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% */