ID INTEGER NOT NULL PRIMARY KEY

,Data VARCHAR(7) NOT NULL

,Cost VARCHAR(20) NOT NULL

,Comments VARCHAR(47) NOT NULL

);

INSERT INTO mytable(ID,Data,Cost,Comments) VALUES (1,'1|2|3','$0.00|$3.17|$42.42','test test||previous thing has a blank comment');

INSERT INTO mytable(ID,Data,Cost,Comments) VALUES (2,'1','$420.69','test');

INSERT INTO mytable(ID,Data,Cost,Comments) VALUES (3,'1|2','$3.50|$4.20','|test');

select id

, convert(varchar(max), null) as datavalue

, convert(varchar(max), null) as costvalue

, convert(varchar(max), null) as commentvalue

, convert(varchar(max), data + '|') as data

, convert(varchar(max), cost + '|') as cost

, convert(varchar(max), comments + '|') as comments

from mytable as t

union all

select id

, convert(varchar(max), left(data, charindex('|', data) - 1))

, convert(varchar(max), left(cost, charindex('|', cost) - 1))

, convert(varchar(max), left(comments, charindex('|', comments) - 1))

, convert(varchar(max), stuff(data, 1, charindex('|', data), ''))

, convert(varchar(max), stuff(cost, 1, charindex('|', cost), ''))

, convert(varchar(max), stuff(comments, 1, charindex('|', comments), ''))

from cte

where (data like '%|%' and cost like '%|%') or comments like '%|%'

)

select id, datavalue, costvalue, commentvalue

from cte

where datavalue IS NOT NULL

order by id, datavalue

set @delimiter = '|'

;with cte as (

select id

, convert(varchar(max), null) as datavalue

, convert(varchar(max), null) as costvalue

, convert(varchar(max), null) as commentvalue

, convert(varchar(max), data + @delimiter) as data

, convert(varchar(max), cost + @delimiter) as cost

, convert(varchar(max), comments + @delimiter) as comments

from mytable as t

union all

select id

, convert(varchar(max), left(data, charindex(@delimiter, data) - 1))

, convert(varchar(max), left(cost, charindex(@delimiter, cost) - 1))

, convert(varchar(max), left(comments, charindex(@delimiter, comments) - 1))

, convert(varchar(max), stuff(data, 1, charindex(@delimiter, data), ''))

, convert(varchar(max), stuff(cost, 1, charindex(@delimiter, cost), ''))

, convert(varchar(max), stuff(comments, 1, charindex(@delimiter, comments), ''))

from cte

where (data like ('%' + @delimiter + '%') and cost like ('%' + @delimiter + '%')) or comments like ('%' + @delimiter + '%')

)

select id, datavalue, costvalue, commentvalue

from cte

where datavalue IS NOT NULL

order by id, datavalue