您可以使用string_to_array函数将字符串的所有部分作为数组的元素，然后对该数组使用unnest函数以获得所需的结果，请检查以下内容：

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

我们可以将STRING_TO_ARRAY与UNNEST和LATERAL JOIN组合使用：

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;

试用：db<>fiddle
STRING_TO_ARRAY会将不同的日期和不同的用户划分为单独的项。
UNNEST将在单独的行中写入这些项。
LATERAL JOIN会将三个日期和三个用户放在一起（当然，根据您的数据，可以更少或更多），这样就创建了问题中显示的九行。它的工作原理与CROSS APPLY方法类似，后者将在SQL Server DB上执行。
ORDER BY子句只是创建了与您的问题中相同的订单，如果不需要，我们可以删除它。问题并没有真正告诉我们是否需要它。

由于实现细节在不同的DBMS上会有所不同，下面是一个如何在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;

参见：DBFIDDLE
注：

• 只有4个值的CROSS JOIN应在存在4个以上项目时展开。
• 没有数据类型连接到获取的值。这个实现不 * 知道 *"2023 - 01 - 08"是，对不起，可以是，一个日期。它只是坚持字符串。

在sql server中，这可以使用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

分贝提琴式链路
https://dbfiddle.uk/YNJWDPBq

在mysql中你可以这样做：

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;

在此查看：https://dbfiddle.uk/_oGix9PD