Query to return a row for each separated value in column

I have some SQL log data like this:

date user actions
2023-01-01 dave changed; added:apple
2023-01-02 gail changed; removed:apple
2023-01-03 mick changed; added:apple; removed:banana; added:cherry; removed:durian
2023-01-04 dave changed; removed:banana; added:cherry

I want a query that splits the actions into separate returned rows, eg actions for user dave in January

date user action item
2023-01-01 dave added apple
2023-01-04 dave removed banana
2023-01-04 dave added cherry

There can be a one or many actions in the source column, like 10 or more. The actual data is a bit more complex; I've simplified to key elements. Typical queries I need would be changes for a particular user or a particular fruit in a time period.

I haven't found anything quite like this problem.


  • As HABO said in comment, string_split() on ; then split on : using charindex(), left() & right().

    You can filter for the required in where clause

    select t.[date], t.[user], a.[action], a.[item]
    from   yourtable t
           cross apply string_split(actions, ';') s
           cross apply
               select [action] = left(s.value, charindex(':', s.value) - 1),
                      [item]   = right(s.value, len(s.value) - charindex(':', s.value))
           ) a
    where  t.[user]       = 'dave'
    and    trim(s.value) <> 'changed'