;WITH CTE AS( SELECT name,V,ROW_NUMBER()OVER(PARTITION BY name ORDER BY GETDATE())RN FROM (SELECT name,CAST('<V>'+REPLACE(value,' ','</V><V>')+'</V>'AS XML)VS FROM TB)T1 CROSS APPLY(SELECT N.V.value('.','VARCHAR(50)')V FROM T1.VS.nodes('/V')N(V))T2 ) SELECT T3.name+'_'+T4.name name,T3.V value FROM CTE T3 JOIN(SELECT 1 RN,'瞬时量'name UNION SELECT 2,'累积量' UNION SELECT 3,'速度')T4 ON T3.RN=T4.RN
with a(name,value) as (select '9a','12 21 2.5' union all select '9b','13 25 23.0' union all select '9c','13 25 1.5')select case when number=1 then a.name+'_瞬时量' when number=2 then a.name+'_累积量' else a.name+'_速度' end as name, case when number=1 then LEFT(value,CHARINDEX(' ',value)-1) when number=2 then LEFT(REPLACE(substring(value,charindex(' ',VALUE)+1,15),' ','@'),CHARINDEX('@', REPLACE(substring(value,charindex(' ',VALUE)+1,15),' ','@'))-1) else right(value,charindex(' ',reverse(value))-1) end as value from a,master..spt_values where type='p' and number>0 and number<4 order by name,number
SELECT name,V,ROW_NUMBER()OVER(PARTITION BY name ORDER BY GETDATE())RN FROM
(SELECT name,CAST('<V>'+REPLACE(value,' ','</V><V>')+'</V>'AS XML)VS FROM TB)T1
CROSS APPLY(SELECT N.V.value('.','VARCHAR(50)')V FROM T1.VS.nodes('/V')N(V))T2
)
SELECT T3.name+'_'+T4.name name,T3.V value FROM CTE T3
JOIN(SELECT 1 RN,'瞬时量'name UNION SELECT 2,'累积量' UNION SELECT 3,'速度')T4 ON T3.RN=T4.RN
我的语句是MSSQL2005+有效然后,你只需要替换TB为你的表名即可最后,如果有问题,希望你能够
是错误就贴错误信息,是效果不符也请指出
(select '9a','12 21 2.5' union all
select '9b','13 25 23.0' union all
select '9c','13 25 1.5')select case when number=1 then a.name+'_瞬时量' when number=2 then a.name+'_累积量'
else a.name+'_速度' end as name,
case when number=1 then LEFT(value,CHARINDEX(' ',value)-1) when number=2 then
LEFT(REPLACE(substring(value,charindex(' ',VALUE)+1,15),' ','@'),CHARINDEX('@',
REPLACE(substring(value,charindex(' ',VALUE)+1,15),' ','@'))-1) else
right(value,charindex(' ',reverse(value))-1) end as value
from a,master..spt_values where type='p' and number>0 and number<4 order by name,number