Pivot in Vertica - From Columns to Rows

Question

I have a table that looks like that:

ID	completion_Rate - step1	completion_Rate - step2	completion_Rate - step3
id1	0	29	54
id2	0	22	76

I want to Pivot it to that:

ID	step_number	completion_Rate
id1	1	0
id1	2	29
id1	3	54
id1	1	0
id2	2	22
id3	3	76

I know we don't have Pivot function in Vertica, but maybe there is a smart way to do it?

(If not, I'll take it a step back, the original table looks like that:

id	completion_rates
id1	0_29_54
id2	0_22_76

So if there is another way to "explode" this string into rows, that doesn't use split part to convert to columns first it will also help me. )

I tried splitting into columns, but now I don't find a way to pivot

Answer 1

From what I see, there are two ways to do it.

If you have large number of columns then maybe use some script to generate the SQL.

Option 1:

WITH input_data (id, completion_rate)
AS (
    SELECT 'id1', '0_29_54'
    UNION ALL
    SELECT 'id2', '0_22_76'
    )
SELECT id, position + 1 as step_number, completion_rate FROM (
SELECT EXPLODE(id, STRING_TO_ARRAY(split_part(completion_rate, '_', 1) 
    || ',' || split_part(completion_rate, '_', 2) 
    || ',' || split_part(completion_rate, '_', 3))::Array [int]) 
    OVER (PARTITION BEST) AS (
        id
        , position
        , completion_rate
        ) FROM input_data
) sub;

 id  | step_number | completion_rate
-----+-------------+-----------------
 id1 |           1 |               0
 id1 |           2 |              29
 id1 |           3 |              54
 id2 |           1 |               0
 id2 |           2 |              22
 id2 |           3 |              76
(6 rows)

Option 2:

WITH input_data (id, completion_rate)
AS (
    SELECT 'id1', '0_29_54'
    UNION ALL
    SELECT 'id2', '0_22_76'
    )
SELECT id
    , 1 as step_number
    , split_part(completion_rate, '_', 1) as completion_rate
FROM input_data

UNION ALL 

SELECT id
    , 2 as step_number
    , split_part(completion_rate, '_', 2) as completion_rate
FROM input_data

UNION ALL 

SELECT id
    , 3 as step_number
    , split_part(completion_rate, '_', 3) as completion_rate
FROM input_data
;


 id  | step_number | completion_rate
-----+-------------+-----------------
 id1 |           1 | 0
 id2 |           1 | 0
 id1 |           2 | 29
 id2 |           2 | 22
 id1 |           3 | 54
 id2 |           3 | 76
(6 rows)

Answer 2

I prefer un-pivoting a delimited string with either and EXPLODE() on a STRING_TO_ARRAY(), as from @minatverma, or by CROSS JOIN-ing with a series of consecutive integers, and using SPLIT_PART():

WITH
-- your input 
indata(id,completion_rates) AS (
          SELECT 'id1','0_29_54'
UNION ALL SELECT 'id2','0_22_76'
)
-- real query starts here, replace following comma with "WITH" ...
,
-- four consecutive integers - one too many ...
i(i) AS (
            SELECT 1
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4
)
SELECT
  id
, i  AS step_number
, SPLIT_PART(completion_rates,'_',i) AS completion_rate
FROM indata CROSS JOIN i
WHERE completion_rate <> '' -- filter out any empty results ...
ORDER BY 1,2;

id	step_number	completion_rate
id1	1	0
id1	2	29
id1	3	54
id2	1	0
id2	2	22
id2	3	76

Answer 3

There's yet another way of doing that - in only one query: Convert the underscore-delimited string to an array, and explode it.

WITH
-- your input 
indata(id,completion_rates) AS (
          SELECT 'id1','0_29_54'
UNION ALL SELECT 'id2','0_22_76'
)
SELECT
  EXPLODE(
    id
  , STRING_TO_ARRAY(completion_rates USING PARAMETERS collection_delimiter='_')
  ) OVER(PARTITION BEST)
FROM indata
ORDER BY 1;                                                                                                                                                                             

id	position	value
id1	0	0
id1	1	29
id1	2	54
id2	0	0
id2	1	22
id2	2	76