StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This should solve your problem:</p> <p><strong>[EDIT]</strong> Your ID is a varchar(100) and you do not specify if it is a primary key, so I made some changes ... ID does't have to be primary key in this case.</p> <pre><code>declare @T table(ID varchar(100), CarData varchar(1000)) declare @OUT table(pk INT IDENTITY(1,1), ID varchar(100), Manufacture varchar(100), Color VARCHAR(100), Cost INT) insert into @T (ID, CarData) values ('1', 'Nissan:blue:20000,Ford:green:10000'), ('2', 'Nissan:steel:20001,Ford:blue:10001,Chevy:blue:10000,Ford:olive:10000') DECLARE @x XML, @i INT, @ID VARCHAR(100), @maxi INT; ;WITH list AS (SELECT pk = ROW_NUMBER() OVER(ORDER BY ID), * FROM @T) SELECT @i=1, @maxi=MAX(pk) FROM list; WHILE @i <= @maxi BEGIN ;WITH list AS (SELECT pk = ROW_NUMBER() OVER(ORDER BY ID), * FROM @T) SELECT @x = CAST( '<root><car><prop>' + REPLACE( REPLACE( CarData ,':' ,'</prop><prop>' ) ,',' ,'</prop></car><car><prop>' ) + '</prop></car></root>' AS XML) , @ID = ID FROM list WHERE pk = @i INSERT INTO @OUT SELECT ID = @ID ,Manufacture = x.value('./prop[1]','VARCHAR(100)') ,Color = x.value('./prop[2]','VARCHAR(100)') ,Cost = x.value('./prop[3]','INT') FROM @x.nodes('/root/car') AS T(x) SET @i = @i + 1; END SELECT * FROM @OUT /* -- OUTPUT 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>

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