StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>-- 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 </code></pre> <p>Result:</p> <pre><code>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 </code></pre> <p><strong>Edit 1</strong></p> <p>You will have trouble with <code>parsename</code> if color, cost or a manufacturer name contains a <code>.</code>. If that is the case you should try this instead.</p> <pre><code>-- 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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload