Pivot from columns to rows?

yardenpel · September 2023

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

mosheg · September 2023

Hi Yarden,

Please try this:

create table MyTable(id varchar, step1 varchar, step2 varchar, step3 varchar);

COPY MyTable FROM STDIN DELIMITER ',' ABORT ON ERROR;
id1,0,29,54,
id2,0,22,76,
\.

SELECT * FROM MyTable;
 id  | step1 | step2 | step3
-----+-------+-------+-------
 id1 | 0     | 29    | 54
 id2 | 0     | 22    | 76
(2 rows)

SELECT id, EXPLODE(ARRAY[step1,step2,step3] USING PARAMETERS explode_count=1, skip_partitioning=true) OVER()
FROM MyTable
ORDER BY 1,2;
 id  | position | value
-----+----------+-------
 id1 |        0 | 0
 id1 |        1 | 29
 id1 |        2 | 54
 id2 |        0 | 0
 id2 |        1 | 22
 id2 |        2 | 76
(6 rows)

VValdar · September 2023

Hi Yarden,

You can use the function string_to_array to convert your original data to the array datatype.
Query is the same than @mosheg 's:

select id
     , explode(string_to_array(completion_rates using parameters collection_delimiter = '_') using parameters explode_count=1, skip_partitioning=true)
  from MyTable
order by 1, 2;

Pivot from columns to rows?

Answers

Leave a Comment