SQL Server 对单个列进行多次拆分

Question

我有一个数据库表，其中有一列包含两个级别的堆叠数据，其中有一列我想要分解一部分。这是数据示例(更改数据是为了保护无辜者:):

表格

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

所以说白了，我需要打破第一个堆叠字段(逗号)并为其创建一行，然后将第二个堆叠字段(冒号)打破成列。

任何帮助将不胜感激。

Answer 1

-- 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:blue:10001,Chevy:blue:10000,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, 1, charindex(',', CarData), '')+','
  from @T
  where len(CarData) > 0
  union all
  select ID,
         cast(substring(CarData, 1, charindex(',', CarData)-1) as varchar(100)),
         stuff(CarData, 1, charindex(',', CarData), '')
  from cte
  where len(CarData) > 0
)
-- Use parsename to split the car data
select ID,
       parsename(replace(Car, ':', '.'), 3) as Manufacture,
       parsename(replace(Car, ':', '.'), 2) as Color,
       parsename(replace(Car, ':', '.'), 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

如果颜色、成本或制造商名称包含 .，您将在使用 parsename 时遇到问题。如果是这种情况，您应该尝试这个。

-- 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:blue:10001,Chevy:blue:10000,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, 1, charindex(',', CarData), '')+','
  from @T
  where len(CarData) > 0
  union all
  select ID,
         cast(substring(CarData, 1, charindex(',', CarData)-1) as varchar(100)),
         stuff(CarData, 1, charindex(',', CarData), '')
  from cte
  where len(CarData) > 0
)
-- Split the car data with substring
select ID,
       substring(Car, 1, P1.Pos-1) as Manufacture,
       substring(Car, P1.Pos+1, P2.Pos-P1.Pos-1) as Color,
       substring(Car, P2.Pos+1, len(Car)-P2.Pos) as Cost
from cte
  cross apply (select charindex(':', Car)) as P1(Pos)
  cross apply (select charindex(':', Car, P1.Pos+1)) as P2(Pos)
order by ID