SnowSQL to Select Day & Time Separately-CodePudding

Source is ONE column of type VARCHAR and it has CSV value:

Monday, 10:30AM–12AM, Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–12AM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM

Desired output in 2 columns, Day and schedule separately

DAY	SCHEDULE
Monday	10:30AM–12AM
Tuesday	10:30AM–12AM
Wednesday	10:30AM–12AM
Thursday	10:30AM–12AM
Friday	10:30AM–12AM
Saturday	10:30AM–12AM
Sunday	10:30AM–12AM

So 1 row with 1 column needs to be translated to 7 rows with 2 columns.

CodePudding user response：

WITH days_of_week AS (
  SELECT * FROM (
    SELECT column1, ROW_NUMBER() OVER (ORDER BY1) AS row_number
    FROM VALUES
        ('Monday'), ('10:30AM–12AM'), ('Tuesday'), ('10:30AM–12AM'), ('Wednesday'), ('10:30AM–12AM'), ('Thursday'), ('10:30AM–12AM'), ('Friday'), ('10:30AM–12AM'), ('Saturday'), ('10:30AM–12AM'), ('Sunday'), ('10:30AM–12AM')
    )
  WHERE row_number % 2 = 1 ),
hours_open AS (
  SELECT * FROM(
    SELECT column1, ROW_NUMBER() OVER (ORDER BY1) AS row_number
    FROM VALUES
        ('Monday'), ('10:30AM–12AM'), ('Tuesday'), ('10:30AM–12AM'), ('Wednesday'), ('10:30AM–12AM'), ('Thursday'), ('10:30AM–12AM'), ('Friday'), ('10:30AM–12AM'), ('Saturday'), ('10:30AM–12AM'), ('Sunday'), ('10:30AM–12AM')
    )
  WHERE row_number % 2 = 0)
SELECT  d.column1 AS Day, h.column1 AS Schedule 
FROM days_of_week d
INNER JOIN hours_open h
ON d.row_number = h.row_number-1;

CodePudding user response：

SELECT src_schedule,DAY,SCHEDULE FROM (
Select  src_schedule
       ,CASE WHEN Index%2=1 THEN VALUE END AS DAY
       ,LEAD(VALUE) OVER(PARTITION BY SEQ ORDER BY INDEX) AS SCHEDULE
From (
Select 'SCHEDULE1' AS src_schedule, 'Monday, 10:30AM–12AM, Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–12AM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM' AS src_columm
Union
Select 'SCHEDULE2' AS src_schedule, 'Tuesday, 10:30AM–12AM, Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–12AM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM' AS src_columm
Union
Select 'SCHEDULE3' AS src_schedule, 'Wednesday, 10:30AM–12AM, Thursday, 10:30AM–12AM, Friday, 10:30AM–12AM, Saturday, 10:30AM–12AM, Sunday, 10:30AM–12AM' AS src_columm
    ) src, table(strtok_split_to_table(src.src_columm,','))
) out WHERE DAY IS NOT NULL
ORDER BY src_schedule
;