Hello I am kinda new to sql. Just wanna know if this is possible via sql:
Table: (Multiple values are in just 1 cell.)
COLUMN 1 | COLUMN 2 |
---|---|
"2023-01-01", "2023-01-02", "2023-01-03" | "User A, User B, User C" |
Needed Output:
COLUMN 1 | COLUMN 2 |
---|---|
2023-01-01 | User A |
2023-01-02 | User A |
2023-01-03 | User A |
2023-01-01 | User B |
2023-01-02 | User B |
2023-01-03 | User B |
2023-01-01 | User C |
2023-01-02 | User C |
2023-01-03 | User C |
Basically, each date from the row is assigned to all users in that same row. Any help or tip will be appreciated.
Thank you!
Screenshot of data/required table
I have no idea yet on how to go around this
5
Answers
Because implementation details van change on different DBMS’s, here is an example of how to do it in MySQL (8.0+):
see: DBFIDDLE
NOTE:
In sql server this can be done using string_split
db fiddle link
https://dbfiddle.uk/YNJWDPBq
In mysql you can do it as follows :
check it here : https://dbfiddle.uk/_oGix9PD
You can use the
string_to_array
function to get all parts of a string as elements of an array, then use theunnest
function on that array to get the desired result, check the following:See demo
We can use a combination of
STRING_TO_ARRAY
withUNNEST
andLATERAL JOIN
here:Try out: db<>fiddle
STRING_TO_ARRAY
will split the different dates and the different users into separate items.UNNEST
will write those items in separate rows.LATERAL JOIN
will put the three dates together with the three users (or of course less/more, depending on your data) and so creates the nine rows shown in your question. It works similar to theCROSS APPLY
approach which will do on a SQL Server DB.The
ORDER BY
clause just creates the same order as shown in your question, we can remove it if not required. The question doesn’t really tell us if it’s needed.