Skip to main content
Question

Pivot from columns to rows?

  • September 21, 2023
  • 2 replies
  • 11 views
yardenpel

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

    2 replies

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • September 21, 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
    Forum|alt.badge.img+1
    • VValdar
    • Participating Frequently
    • September 22, 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;
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement