Un concat a column SQL

Question

I have a concatenated column(order_type) like this:

     OrderType
Data;Let;Data;Data;Let

I want to create two columns from the above column

Data Let
 3    2

Answer 1

Fixing your data model, while the correct approach, probably can't be done immediately so a hacky workaround is to just use some simple string processing:

select 
 (length(ordertype) - length(Replace(ordertype,'Data',''))) / Length('Data') as Data,
 (length(ordertype) - length(Replace(ordertype,'Let',''))) / Length('Let') as Let
from t