how can I extract values from one cell to make it multiple rows if i had a table like this:


------------------------------------------
user  | list_of_jobs                       |
------------------------------------------
1     |   [dentist] | [farmer] | [fisher]  |
------------------------------------------
2     |   [doctor]                         |
------------------------------------------



how can I convert that to something like this:


----------------------------------------------
user  | job                                  |
----------------------------------------------
1     |  dentist                              |
----------------------------------------------
1     |  farmer                               |
----------------------------------------------
1     |  fisher                               |
----------------------------------------------
2     |  docter                               |
----------------------------------------------
If the "list_of_jobs" is a known set of values, you can use UNPIVOT to do this, see this document

If it is an unknown set of values, you can use STRING_SPLIT in SQL Server 2016 or higher, see this document. NOTE: Your database compatibility level needs to be 130 or higher.

If you have SQL Server 2014 or earlier, or your compatibility level is below 130, you will need to write a user function to do the string splitting. Here's a good example.