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.
3条答案
按热度按时间0qx6xfy61#
As HABO said in comment, string_split() on
;
then split on:
usingcharindex()
,left()
&right()
.You can filter for the required in
where
clause9njqaruj2#
Please try the following solution based on
STRING_SPLIT()
andPARSENAME()
functions.Assumption is that the actions column values don't contain dots.
SQL
Output
0tdrvxhp3#
perhaps something like this