SQL query multiple values in just one cell-CodePudding

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

CodePudding user response：

Because implementation details van change on different DBMS's, here is an example of how to do it in MySQL (8.0 ):

WITH column1 as (
   SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(column1,',',x),',',-1)) as Value
   FROM test
   CROSS JOIN (select 1 as x union select 2 union select 3 union select 4) x
   WHERE x <= LENGTH(Column1)-LENGTH(REPLACE(Column1,',','')) 1
  ),
  column2 as (
   SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(column2,',',x),',',-1)) as Value
   FROM test
   CROSS JOIN (select 1 as x union select 2 union select 3 union select 4) x
   WHERE x <= LENGTH(Column2)-LENGTH(REPLACE(Column2,',','')) 1
  )
SELECT * 
FROM column1, column2;

see: DBFIDDLE

NOTE:

The CROSS JOIN, with only 4 values should be expanded when more than 4 items exist.
There is not data type connected to the values that are fetched. This implementation does not know that "2023-01-08" is, sorry CAN BE, a date. It just sticks to strings.

CodePudding user response：

In sql server this can be done using string_split

select x.value as date_val,y.value as user_val
 from test a
CROSS APPLY string_split(Column1,',')x
CROSS APPLY string_split(Column2,',')y
order by y.value,x.value

date_val    user_val
2023-01-01  User A
2023-01-02  User A
2023-01-03  User A
2023-01-03  User B
2023-01-02  User B
2023-01-01  User B
2023-01-01  User C
2023-01-02  User C
2023-01-03  User C

db fiddle link

https://dbfiddle.uk/YNJWDPBq

CodePudding user response：

In mysql you can do it as follows :

WITH dates as ( 
  select TRIM(SUBSTRING_INDEX(_date, ',', 1)) AS 'dates'
  from _table
  union 
  select TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(_date, ',', 2), ',', -1)) AS 'dates'
  from _table
  union
  select TRIM(SUBSTRING_INDEX(_date, ',', -1)) AS 'dates'
  from _table
),
users as
  ( select TRIM(SUBSTRING_INDEX(user, ',', 1)) AS 'users'
  from _table
  union 
  select TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(user, ',', 2), ',', -1)) AS 'users'
  from _table
  union
  select TRIM(SUBSTRING_INDEX(user, ',', -1)) AS 'users'
  from _table
)
select *
from dates, users
order by dates, users;

check it here : https://dbfiddle.uk/_oGix9PD