I am trying to query a database that has multiple values in a single column that I need to separate into different columns. For example:
tbl1.clm1.value1 as column value1
tbl1.clm1.value2 as column value2
Any help would be greatly appreciated!
By definition, this is not a table or a column. It is a clumsy violation of First Normal Form (1NF), which is the foundation of the relational model.
Your best bet is to go to the input or ETL layers and clean up the crap data before it gets to the database layer. Doing this in the database layer with splitter procedures is like mopping the floor instead of fixing the broken toilet so that you never have to deal with all the problems. Are you really smart enough to write the entire I/O library for T-SQL in T-SQL? With all the errors messages and optimizations? And keep it updated?