skip to Main Content

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


  1. 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.
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
  3. 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

    Login or Signup to reply.
  4. You can use the string_to_array function to get all parts of a string as elements of an array, then use the unnest function on that array to get the desired result, check the following:

    select col1,
      unnest(string_to_array(replace(replace(COLUMN2,'"',''),', ',','), ',')) as col2
    from 
    (
      select unnest(string_to_array(replace(replace(COLUMN1,'"',''),', ',','), ',')) as col1
             , COLUMN2
      from table_name
    ) T
    order by col1, col2
    

    See demo

    Login or Signup to reply.
  5. We can use a combination of STRING_TO_ARRAY with UNNEST and LATERAL JOIN here:

    SELECT col1.column1, col2.column2
    FROM
    (SELECT UNNEST(
      STRING_TO_ARRAY(column1,',')
    ) AS column1 FROM test) col1
    LEFT JOIN LATERAL
    (SELECT UNNEST(
      STRING_TO_ARRAY(column2,',')
    ) AS column2 FROM test) col2
    ON true
    ORDER BY col2.column2, col1.column1;
    

    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 the CROSS 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search