Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Are there sql commands to pivot data?


  • I prefer an implementation like the one below if possible:

  • Hi!

    You can try to open a feature request. Post under topic IDEAS

  • Hm... took a look on tablefunc... its not so hard to implement it with UDF. Just for fun I implemented an easiest function -  normal_rand(numrows, mean, stddev)

    dev=> select * from user_functions where function_name ilike '%normal%';
    -[ RECORD 1 ]----------+-----------------------------------------------------
    schema_name | public
    function_name | normal_rand
    procedure_type | User Defined Transform
    function_return_type | Float
    function_argument_type |
    function_definition | Class 'NormRandFactory' in Library 'public.Gaussian'
    volatility |
    is_strict | f
    is_fenced | t
    comment |

    dev=> select normal_rand(USING PARAMETERS rows=3, mean=3.3, stddev=6.3) over (PARTITION AUTO);
    (3 rows)

    dev=> \! lscpu | grep -P '(^CPU.s.)|(MHz)'
    CPU(s): 8
    CPU MHz: 1200.000
    dev=> \o /dev/null
    dev=> \timing
    Timing is on.

    1MIL rows:
    dev=> select normal_rand(USING PARAMETERS rows=1000000, mean=3.3, stddev=6.3) over (PARTITION AUTO);
    Time: First fetch (1000 rows): 72.207 ms. All rows formatted: 1405.582 ms
    2,5MIL rows:
    dev=> select normal_rand(USING PARAMETERS rows=2500000, mean=3.3, stddev=6.3) over (PARTITION AUTO);
    Time: First fetch (1000 rows): 68.825 ms. All rows formatted: 3521.669 ms
    5MIL rows:
    dev=> select normal_rand(USING PARAMETERS rows=5000000, mean=3.3, stddev=6.3) over (PARTITION AUTO);
    Time: First fetch (1000 rows): 72.705 ms. All rows formatted: 7135.159 ms
    Nice linear scalability
    | ROWS | TIME(s)|
    | 1000000 | 1.4 |
    | 2500000 | 3.5 |
    | 5000000 | 7.1 |
    I can share a code if you are interesting in this function. May be (also just for fun) I will implement others functions too. Can you tell me witch function is more important for you? And I will start from this function.

  • Great !  !

    crosstab !

    see you soon :-  )

  • Hi!

    I will try, but syntax will be differ from PG implementation.
    I will update you in any case - success or failed.

  • As I got to say, it would be important for people like me who uses the database as a data source for simulations and statistical models of machine learning, have a function that creates horizontal metrics that were calculated, I thank you and I appreciate your help, I hope also that hp-vertica may notice your work and adopt it as soon as helping you develop it.
  • Hi!

    I failed. I will explain where I failed.

    Implementation of PIVOT require:
    • table data
    • cardinality of PIVOT'ed column (its dynamic and mutable and its a problem)
    The only way I can implement it - via external procedures or UDF.
    Unfortunately UDF is out of scope:
    • Vertica will try to parallelize UDF
    • I need to know a cardinality of pivoted data. I need it to define a pivoted table columns. Of cause I can use in ODBC/JDBC and create a Flex table and so columns will be dynamic, but Vertica will parallelize it and you will get a garbage.
    How I see it with EP?
    vsql=> select pivot(src_table=<Table>, dest_table=<Table>, pivot_column=<column>);
    • src_table - an original data for pivoting
    • pivot_column -  pivoted column
    • dest_table - EP will insert results to this table
    Or suggest your syntax(but take in mind - I must know how many columns will be in a new table and so I have to query a pivoted column for cardinality)

    Will you accept solution with EP?

    The main problem - is cardinality of pivoted column. I can limit query execution on one node only, but I can't create table with dynamic columns.
  • Gaussian Distribution source code:

    g++ -std=c++11 -D HAVE_LONG_INT_64  -I /opt/vertica/sdk/include -Wall -shared -Wno-unused-value -fPIC -o NormalDistribution.cpp /opt/vertica/sdk/include/Vertica.cpp
    CREATE LIBRARY Gaussian AS '/tmp/';
    CREATE TRANSFORM FUNCTION normal_rand AS LANGUAGE 'C++' NAME 'NormalDistributionFactory' LIBRARY Gaussian;
  • Hello  Genius !,  " id10t " it seems not appropriate to you,
    do you think this could be a possible solution (from oracle 11g) ?

    select * from( select deptno, job, sal from emp ) e 

    pivot(   sum (sal )  for job in ( 'CLERK', 'SALESMAN', 'MANAGER', 'ANALYST', 'PRESIDENT' ) )

    order by deptno        DEPTNO     'CLERK'     'SALESMAN'      'MANAGER'    'ANALYST'     'PRESIDENT' -----------   --------    ------------   ------------   ----------   -------------          10       1300                           2450                         5000          20       1900                           2975         6000          30        950           5600            2850


  • Hi!

    Q: How can you query a database from UDF for pivot?
    A: Only via ODBC or JDBC connection. So I have to create a connection to fetch data for pivot. Vertica isn't released a native connector and UDF doesn't support "data fetch" from database.

    Q: And is it a problem?
    A: Yes, Vertica parallelizes UDF and so many connections are opened. And Vertica raises exception, because each connection tries to create pivot table and to insert a pivot data to it. First tread succeeded but others throws an exception. I can't use in "OVER (PARTITION AUTO)" because i need "OVER ()" to define a pivot column.

    Q: Does Oracle approach can be implemented with UDF?
    A: Hm... interesting. I will try. Its more suitable, since I don't need to know a cardinality. Cardinality defined in query. Nice.

    Q: But you still need to fetch data from database. How you will limit a query to a single execution?
    • I can insert data to temporary table(but Im not sure it will solve a problem with "many connections")
    • I can define "CREATE TABLE IF NOT EXISTS", so a second thread will trow an warning only.
    • If these method will fail so I have a last option to limit a query to a single thread execution. I can define witch projections to use
    SELECT set_optimizer_directives('AvoidUsingProjections=prj_sup,prj_rep');
    I have no more options. May be someone will suggest a something?

    Q: And what if a projection is segmented?
    A: So far I can't answer, I need to investigate it, but Im afraid that without a help from Vertica Support I can't implement it with UDF if table is segmented.

    May be Vertica Support will provide a hint that limits a query to a single thread?

    Hm... and what about CPU Affinity? I will try, looks like "CREATE TABLE IF NOT EXISTS" solves a problem with exceptions.
    I will update.

  • Hi Massimo.

    Yes, it is possible with UDF Multi-Phase Transform function, but I compared a performance with DECODE function - performance is under any critics.  So far my code have some assumptions(read bugs), because I simplified a problem, just wanted to understand - can I do it?


    When I will fix a bugs I will publish it.  Feel free to remind.


    But I recommend you to compare a performance of some UDF function against a built-in function. For example, Vertica provides an UDF Aggregate function - AVG:
    and take in mind that Transform Multi-Phase will be x100(at least) slower than doing it with DECODE.


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.