SQL Server多次拆分单个列

表

ID = varchar(100)
CarData = varchar(1000)

ID       CarData
1        Nissan:blue:20000,Ford:green:10000
2        Nissan:steel:20001,Ford:blue:10001,Chevy:blue:10000,Ford:olive:10000
** Note that cardata can is not fixed,and can have many cars in it

输出所需：

ID   Manufacture    Color     Cost
1    Nissan         Blue      20000
1    Ford           green     10000
2    Nissan         steel      20001
... and on

所以说明白我需要打破第一个堆叠字段,这是一个逗号并为其创建一行,然后将第二个堆叠字段分解为列.

任何帮助将不胜感激.

解决方法

-- Sample data
declare @T table(ID int,CarData varchar(100))
insert into @T values 
(1,'Nissan:blue:20000,Ford:green:10000'),(2,'Nissan:steel:20001,Ford:olive:10000')

-- Recursice CTE to get one row for each car
;with cte(ID,Car,CarData) as
(
  select ID,cast(substring(CarData+',',1,charindex(',CarData+',')-1) as varchar(100)),stuff(CarData,CarData),'')+','
  from @T
  where len(CarData) > 0
  union all
  select ID,cast(substring(CarData,CarData)-1) as varchar(100)),'')
  from cte
  where len(CarData) > 0
)
-- Use parsename to split the car data
select ID,parsename(replace(Car,':','.'),3) as Manufacture,2) as Color,1) as Cost
from cte
order by ID

结果：

ID  Manufacture  Color   Cost
--  -----------  ------   -----
1   Nissan       blue    20000
1   Ford         green   10000
2   Nissan       steel   20001
2   Ford         blue    10001
2   Chevy        blue    10000
2   Ford         olive   10000

编辑1

如果颜色,成本或制造商名称包含a,那么你将遇到解析名称的问题.如果是这种情况,你应该尝试这样做.

-- Sample data
declare @T table(ID int,'')
  from cte
  where len(CarData) > 0
)
-- Split the car data with substring
select ID,substring(Car,P1.Pos-1) as Manufacture,P1.Pos+1,P2.Pos-P1.Pos-1) as Color,P2.Pos+1,len(Car)-P2.Pos) as Cost
from cte
  cross apply (select charindex(':',Car)) as P1(Pos)
  cross apply (select charindex(':',P1.Pos+1)) as P2(Pos)
order by ID

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!