WITH RawData AS (
        SELECT 1 AS Sequence_ID, 'program6' AS Program_Name
        UNION ALL
        SELECT 2, 'program2'
        UNION ALL
        SELECT 3, 'program7'
        UNION ALL
        SELECT 4, 'program3'
        UNION ALL
        SELECT 5, 'program1'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 6, NULL
        UNION ALL
        SELECT 7, NULL
        UNION ALL
        SELECT 8, NULL
               -- Continue with program names
        UNION ALL
        SELECT 9, 'program4'
        UNION ALL
        SELECT 10, 'program1'
        UNION ALL
        SELECT 11, 'program2'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 12, NULL
        UNION ALL
        SELECT 13, NULL
               -- Continue with program names
        UNION ALL
        SELECT 14, 'program6'
        UNION ALL
        SELECT 15, 'program1'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 16, NULL
        UNION ALL
        -- Continue with program names
        SELECT 17, 'program5'
        UNION ALL
        SELECT 18, 'program2'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 19, NULL
               -- Continue with program names
        UNION ALL
        SELECT 20, 'program3'
        UNION ALL
        SELECT 21, 'program4'
        UNION ALL
        SELECT 22, 'program2'
        UNION ALL
        SELECT 23, 'program5'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 24, NULL
        UNION ALL
        SELECT 25, NULL
               -- Continue with program names
        UNION ALL
        SELECT 26, 'program1'
        UNION ALL
        SELECT 27, 'program7'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 28, NULL
               -- Continue with program names
        UNION ALL
        SELECT 29, 'program6'
        UNION ALL
        SELECT 30, 'program4'
        UNION ALL
        SELECT 31, 'program2'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 32, NULL
               -- Continue with program names
        UNION ALL
        SELECT 33, 'program5'
        UNION ALL
        SELECT 34, 'program3'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 35, NULL
        UNION ALL
        SELECT 36, NULL
               -- Continue with program names
        UNION ALL
        SELECT 37, 'program2'
        UNION ALL
        SELECT 38, 'program1'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 39, NULL
               -- Continue with program names
        UNION ALL
        SELECT 40, 'program6'
        UNION ALL
        SELECT 41, 'program4'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 42, NULL
        UNION ALL
        -- Continue with program names
        SELECT 43, 'program5'
        UNION ALL
        SELECT 44, 'program2'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 45, NULL
               -- Continue with program names
        UNION ALL
        SELECT 46, 'program1'
        UNION ALL
        SELECT 47, 'program7'
               -- Rows with no Program_Name
        UNION ALL
        SELECT 48, NULL
               -- Continue with program names
        UNION ALL
        SELECT 49, 'program3'
        UNION ALL
        SELECT 50, 'program5'
     ),
     pivotdata AS (
        SELECT
            sequence_id,
            program_name,
            ROW_NUMBER() OVER (PARTITION BY CASE WHEN program_name IS NOT NULL THEN 0 ELSE 1 END ORDER BY sequence_id ASC) AS rno_asc,
            ROW_NUMBER() OVER (PARTITION BY CASE WHEN program_name IS NOT NULL THEN 0 ELSE 1 END ORDER BY sequence_id DESC) AS rno_desc,
            CASE
                WHEN
                    FIRST_VALUE(program_name) OVER (PARTITION BY CASE WHEN program_name IS NOT NULL THEN 0 ELSE 1 END ORDER BY sequence_id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) =
                    LAST_VALUE(program_name) OVER (PARTITION BY CASE WHEN program_name IS NOT NULL THEN 0 ELSE 1 END ORDER BY sequence_id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                    THEN 1
            END initial_and_final_flag
        FROM
            rawdata
     )
-- SELECT * FROM pivotdata
SELECT
    DECODE(MAX(CASE WHEN program_name = 'program1' THEN appearance END), 2, 'initial', 1, 'in_between', 3, 'final', 4, 'initial_and_final') AS program1,
    DECODE(MAX(CASE WHEN program_name = 'program2' THEN appearance END), 2, 'initial', 1, 'in_between', 3, 'final', 4, 'initial_and_final') AS program2,
    DECODE(MAX(CASE WHEN program_name = 'program3' THEN appearance END), 2, 'initial', 1, 'in_between', 3, 'final', 4, 'initial_and_final') AS program3,
    DECODE(MAX(CASE WHEN program_name = 'program4' THEN appearance END), 2, 'initial', 1, 'in_between', 3, 'final', 4, 'initial_and_final') AS program4,
    DECODE(MAX(CASE WHEN program_name = 'program5' THEN appearance END), 2, 'initial', 1, 'in_between', 3, 'final', 4, 'initial_and_final') AS program5,
    DECODE(MAX(CASE WHEN program_name = 'program6' THEN appearance END), 2, 'initial', 1, 'in_between', 3, 'final', 4, 'initial_and_final') AS program6,
    DECODE(MAX(CASE WHEN program_name = 'program7' THEN appearance END), 2, 'initial', 1, 'in_between', 3, 'final', 4, 'initial_and_final') AS program7,
    DECODE(MAX(CASE WHEN program_name = 'program8' THEN appearance END), 2, 'initial', 1, 'in_between', 3, 'final', 4, 'initial_and_final') AS program8
FROM
    (
        SELECT
            program_name,
            CASE
                WHEN program_name IS NOT NULL and rno_asc = 1 AND initial_and_final_flag = 1
                    THEN 4
                WHEN program_name IS NOT NULL and rno_desc = 1
                    THEN 3
                WHEN program_name IS NOT NULL and rno_asc = 1
                    THEN 2
                WHEN program_name IS NOT NULL
                    THEN 1
            END AS appearance
        FROM
            pivotdata
    ) AS pivoteddata

+----------+----------+----------+----------+--------+--------+----------+--------+
|program1  |program2  |program3  |program4  |program5|program6|program7  |program8|
+----------+----------+----------+----------+--------+--------+----------+--------+
|in_between|in_between|in_between|in_between|final   |initial |in_between|null    |
+----------+----------+----------+----------+--------+--------+----------+--------+

+----------+----------+----------+----------+----------+--------------------+----------+--------+
|program1  |program2  |program3  |program4  |program5  |program6            |program7  |program8|
+----------+----------+----------+----------+----------+--------------------+----------+--------+
|in_between|in_between|in_between|in_between|in_between|initial_and_final   |in_between|null    |
+----------+----------+----------+----------+----------+--------------------+----------+--------+

+----------+----------+----------+----------+----------+---------+----------+--------+
|program1  |program2  |program3  |program4  |program5  |program6 |program7  |program8|
+----------+----------+----------+----------+----------+---------+----------+--------+
|null      |null      |null      |null      |null      |single   |null      |null    |
+----------+----------+----------+----------+----------+---------+----------+--------+

with
  raw_data as ( ...... )
, prep as (
    select case grouping(program_name) when 0 then program_name else 'global' end as program_name,
           grouping_id(program_name) as lvl,
           min(case grouping(program_name) when 1 then min(program_name) end) over () as min_pn,
           min(case grouping(program_name) when 1 then max(program_name) end) over () as max_pn,
           min(case grouping(program_name) when 1 then min(program_name)
                            keep (dense_rank first order by sequence_id) end) over () as first_pn,
           min(case grouping(program_name) when 1 then min(program_name)
                            keep (dense_rank  last order by sequence_id) end) over () as last_pn
    from   rawdata
    group  by rollup(program_name)
  )
select *
from   ( select program_name, min_pn, max_pn, first_pn, last_pn
         from   prep
         where  lvl = 0
       )
pivot  ( min( case when min_pn = max_pn then case when program_name = min_pn then 'single' end
                   when first_pn = last_pn and program_name = first_pn       then 'initial_and_final'
                   when program_name = first_pn then 'initial'
                   when program_name = last_pn  then 'final'
                   else                              'in_between'
              end )
         for program_name in ('program1' as program1, 'program2' as program2, 'program3' as program3, 'program4' as program4,
                              'program5' as program5, 'program6' as program6, 'program7' as program7, 'program8' as program8)
       )
;

...
from  rawdata
where program_name is not null
group by rollup(program_name)
...

where program_name in ('program1', 'program2', ...... , 'program8')