我有一个数据库表,其中包含一个堆叠数据的列,其中包含两个级别,我希望打破一个部分.以下是数据示例(为保护无辜者而改变数据:)：
表
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
