Dynamic Header Column When Exec Query

relireli Vertica Customer

Hi,
I have 2 tables A & B
CREATE TABLE tst.tableB(id int,code_ varchar(30));
CREATE TABLE tst.tableA(id int,code_ varchar(30));
A and B have the join column id.
the value in A:
id |code_|
-- |-----|
1 |a1 |
2 |a2|
the value in B:
id |code_|
-- |-----|
1 |b1 |
1 |b2 |
I want the result of the select query looks like this:
b1 | b2
-- |----
a1|a2
I need a dynamic header column from the b table, the hander can be changed in each select query.
Does anyone have an idea how to do this?

Best Answers

  • Ariel_CaryAriel_Cary Vertica Employee Employee
    Answer ✓

    You could use some flexibility to transform and query your data. Let's try this with flex tables.

    First, let's do some setup.

    create schema tst;
    CREATE TABLE tst.tableB(id int,code_ varchar(30));
    CREATE TABLE tst.tableA(id int,code_ varchar(30));
    
    insert into tst.tableA values(1, 'a1');
    insert into tst.tableA values(2, 'a2');
    insert into tst.tableB values(1, 'b1');
    insert into tst.tableB values(2, 'b2'); -- with the right id
    
    commit;
    

    Check we get our data right.

    select b.code_ header, a.code_ value from tst.tableA a, tst.tableB b where a.id=b.id;
     header | value 
    --------+-------
     b1     | a1
     b2     | a2
    (2 rows)
    

    Now, load your header/value pairs into a flex table VMap. Key your data by header.

    create flex table tst.f1();
    insert into tst.f1
        select mapaggregate(header, value) over() as __raw__ 
        from (select b.code_ header, a.code_ value from tst.tableA a, tst.tableB b where a.id=b.id) t;
    commit;
    

    You are ready to query the flex table to get your data in the format you wanted.

    select b1, b2 from tst.f1;
     b1 | b2 
    ----+----
     a1 | a2
    (1 row)
    

    The previous query should be enough if you know the header names.
    In your case, your header names can change dynamically. For that, we will ask Vertica to discover them for you.

    select compute_flextable_keys_and_build_view('tst.f1');
                           compute_flextable_keys_and_build_view                        
    ------------------------------------------------------------------------------------
     Please see tst.f1_keys for updated keys
    The view tst.f1_view is ready for querying
    (1 row)
    

    Now select all columns from the view that was just created. It will include all header names in table tst.B without you needing to type them.

    select * from tst.f1_view;
     b1 | b2 
    ----+----
     a1 | a2
    (1 row)
    
  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    Answer ✓

    @Ariel_Cary - That is a cool way to use a Flex Table!

    @reli - Just for fun, here is maybe another way to get what you want on the fly from vsql...

    dbadmin=> SELECT * FROM tst.tableA;
     id | code_
    ----+-------
      1 | a1
      2 | a2
    (2 rows)
    
    dbadmin=> SELECT * FROM tst.tableB;
     id | code_
    ----+-------
      1 | b1
      2 | b2
    (2 rows)
    
    dbadmin=> \! vsql -Atc "SELECT 'SELECT' UNION ALL (SELECT '''' || a.code_ || ''' AS \"' || b.code_ || '\"' || DECODE(LEAD(a.code_) OVER(ORDER BY 1), NULL, ';', ',') FROM tst.tableA a JOIN tst.tableB b USING (id) oRDER BY a.id)" | vsql
     b1 | b2
    ----+----
     a1 | a2
    (1 row)
    
    dbadmin=> UPDATE tst.tableB SET code_ = 'My new header name' WHERE id = 2;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> COMMIT;
    COMMIT
    
    dbadmin=> \! vsql -Atc "SELECT 'SELECT' UNION ALL (SELECT '''' || a.code_ || ''' AS \"' || b.code_ || '\"' || DECODE(LEAD(a.code_) OVER(ORDER BY 1), NULL, ';', ',') FROM tst.tableA a JOIN tst.tableB b USING (id) oRDER BY a.id)" | vsql
     b1 | My new header name
    ----+--------------------
     a1 | a2
    (1 row)
    
    dbadmin=> INSERT INTO tst.tableA SELECT 3, 'a3';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> INSERT INTO tst.tableB SELECT 3, 'A new column value!';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> COMMIT;
    COMMIT
    
    dbadmin=> \! vsql -Atc "SELECT 'SELECT' UNION ALL (SELECT '''' || a.code_ || ''' AS \"' || b.code_ || '\"' || DECODE(LEAD(a.code_) OVER(ORDER BY 1), NULL, ';', ',') FROM tst.tableA a JOIN tst.tableB b USING (id) oRDER BY a.id)" | vsql
     b1 | My new header name | A new column value!
    ----+--------------------+---------------------
     a1 | a2                 | a3
    (1 row)
    

    I think that works :)

  • relireli Vertica Customer
    Answer ✓

    my mistake :#, I add now a better example of my problem:

    create schema tst;
    CREATE TABLE tst.tableA( id int,c1_ varchar(30), c2_ varchar(30),c3_ varchar(30));
    CREATE TABLE tst.tableB( id int,w1_ varchar(30), w2_ varchar(30),w3_ varchar(30));
    insert into tst.tableA values(1, 'c1','c2','c3');
    insert into tst.tableB values(1, 'w1','w2','w3');
    create flex table tst.f2();
    

    then I insert the join raw from both tables (only one raw ) to the flex table:
    `

    insert into tst.f2
        select mapaggregate(header, value) over() as __raw__ 
        from (select b.w1_ header, a.c1_ value from tst.tableA a, tst.tableB b where a.id=1 and b.id=1) t;
    insert into tst.f2
        select mapaggregate(header, value) over() as __raw__ 
        from (select b.w2_ header, a.c2_ value from tst.tableA a, tst.tableB b where a.id=1 and b.id=1) t;
    insert into tst.f2
        select mapaggregate(header, value) over() as __raw__ 
        from (select b.w3_ header, a.c3_ value from tst.tableA a, tst.tableB b where a.id=1 and b.id=1) t;
    select compute_flextable_keys_and_build_view('tst.f2');
    

    when selecting the view now

    select * from tst.f2_view;
    -- the result
    w1      |  w2       |    w3   |
    ------+--------+-------|
    NULL |  NULL | c3       |
    c1       | NULL  | NULL |
    NULL | c2        | NULL |
    
    the result I want is :
    w1      |  w2       |    w3   |
    ------+--------+-------|
    c1      |    c2      |   c3
    

    `
    Is it possible? :smile:

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2020 Answer ✓

    Simple answer:

    dbadmin=> select MAX(w1) w1, MAX(w2) w2, MAX(w3) w3 from tst.f2_view;
     w1 | w2 | w3
    ----+----+----
     c1 | c2 | c3
    (1 row)
    

    But that's not very dynamic.

    Or you could change the insert:

    dbadmin=> truncate table tst.f2;
    TRUNCATE TABLE
    
    dbadmin=> insert into tst.f2 select mapaggregate(header, value) over() as __raw__ from (select b.w1_ header, a.c1_ value from tst.tableA a, tst.tableB b where a.id=b.id UNION ALL select b.w2_ header, a.c2_ value from tst.tableA a, tst.tableB b where a.id=b.id UNION ALL select b.w3_ header, a.c3_ value from tst.tableA a, tst.tableB b where a.id=b.id) t;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> COMMIT;
    COMMIT
    
    dbadmin=> select compute_flextable_keys_and_build_view('tst.f2');
                           compute_flextable_keys_and_build_view
    ------------------------------------------------------------------------------------
     Please see tst.f2_keys for updated keys
    The view tst.f2_view is ready for querying
    (1 row)
    
    dbadmin=> select * from tst.f2_view;
     w1 | w2 | w3
    ----+----+----
     c1 | c2 | c3
    (1 row)
    

    Fyi .. This works too:

    dbadmin=> SELECT * FROM tst.tableA;
     id | c1_ | c2_ | c3_
    ----+-----+-----+-----
      1 | c1  | c2  | c3
    (1 row)
    
    dbadmin=> SELECT * FROM tst.tableB;
     id | w1_ | w2_ | w3_
    ----+-----+-----+-----
      1 | w1  | w2  | w3
    (1 row)
    
    dbadmin=>  \! vsql -Atc "SELECT 'SELECT' UNION ALL (SELECT '''' || a.c1_ || ''' AS \"' || b.w1_ || '\", ' || '''' || a.c2_ || ''' AS \"' || b.w2_|| '\", ' || '''' || a.c3_ || ''' AS \"' || b.w3_|| '\"' || DECODE(LEAD(a.c1_) OVER(ORDER BY 1), NULL, ';', ',') FROM tst.tableA a JOIN tst.tableB b USING (id) ORDER BY a.id)" | vsql
     w1 | w2 | w3
    ----+----+----
     c1 | c2 | c3
    (1 row)
    
    
  • relireli Vertica Customer
    Answer ✓

    @Jim_Knicely said:
    Simple answer:

    dbadmin=> select MAX(w1) w1, MAX(w2) w2, MAX(w3) w3 from tst.f2_view;
     w1 | w2 | w3
    ----+----+----
     c1 | c2 | c3
    (1 row)
    

    But that's not very dynamic.

    Or you could change the insert:

    dbadmin=> truncate table tst.f2;
    TRUNCATE TABLE
    
    dbadmin=> insert into tst.f2 select mapaggregate(header, value) over() as __raw__ from (select b.w1_ header, a.c1_ value from tst.tableA a, tst.tableB b where a.id=b.id UNION ALL select b.w2_ header, a.c2_ value from tst.tableA a, tst.tableB b where a.id=b.id UNION ALL select b.w3_ header, a.c3_ value from tst.tableA a, tst.tableB b where a.id=b.id) t;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> COMMIT;
    COMMIT
    
    dbadmin=> select compute_flextable_keys_and_build_view('tst.f2');
                           compute_flextable_keys_and_build_view
    ------------------------------------------------------------------------------------
     Please see tst.f2_keys for updated keys
    The view tst.f2_view is ready for querying
    (1 row)
    
    dbadmin=> select * from tst.f2_view;
     w1 | w2 | w3
    ----+----+----
     c1 | c2 | c3
    (1 row)
    

    Fyi .. This works too:

    dbadmin=> SELECT * FROM tst.tableA;
     id | c1_ | c2_ | c3_
    ----+-----+-----+-----
      1 | c1  | c2  | c3
    (1 row)
    
    dbadmin=> SELECT * FROM tst.tableB;
     id | w1_ | w2_ | w3_
    ----+-----+-----+-----
      1 | w1  | w2  | w3
    (1 row)
    
    dbadmin=>  \! vsql -Atc "SELECT 'SELECT' UNION ALL (SELECT '''' || a.c1_ || ''' AS \"' || b.w1_ || '\", ' || '''' || a.c2_ || ''' AS \"' || b.w2_|| '\", ' || '''' || a.c3_ || ''' AS \"' || b.w3_|| '\"' || DECODE(LEAD(a.c1_) OVER(ORDER BY 1), NULL, ';', ',') FROM tst.tableA a JOIN tst.tableB b USING (id) ORDER BY a.id)" | vsql
     w1 | w2 | w3
    ----+----+----
     c1 | c2 | c3
    (1 row)
    
    

    Thank you so much it's great!!! :blush:

Answers

  • Sudhakar_BSudhakar_B Vertica Customer ✭✭

    @reli
    I think this is what your are looking for: https://github.com/dingqiangliu/vertica_pivot.
    This provides both standard ANSI SQL way and Vertica UDTF to achieve PIVOT/UNPIVOT.

  • relireli Vertica Customer

    PIVOT/UNPIVOT only changed the raw to column in the result and the header in the example are const '2003-01-01...', I need the
    raw be column and the dynamic header of the table , I don't have const value in raw.

  • Sudhakar_BSudhakar_B Vertica Customer ✭✭

    Aah OK.
    Is there a "maximum number" of header values you would have?
    Also, looking at your example how is the header b2 get related to value a2? Using ID to join will not provide that.
    Can you please elaborate on the requirement.

  • relireli Vertica Customer

    Is there a "maximum number" of header values you would have?
    Yes, the table b with real values had 260 raws who need to be the header
    Also, looking at your example how is the header b2 get related to value a2? Using ID to join will not provide that.
    Yes you are right I update the data

    id code_
    1 b1
    2 b2
  • Sudhakar_BSudhakar_B Vertica Customer ✭✭

    Very nice use of flex table @Ariel_Cary ! Excellent.

  • relireli Vertica Customer

    The best solution I could get, thank you very much!! o:)

  • relireli Vertica Customer

    Now I have a similar problem I would thank you if you help with the previous problem, you really saved me!! :smiley:
    now I have table c with a timestamp column inner join to b as flowing :

    CREATE TABLE tst.tableC(id int,ts timestamptz,cl1_ varchar(30),cl2_ varchar(30),cl3_ varchar(30));
       INSERT INTO tst.tableC VALUES(1, '2020-12-22', 'value1', 'value2', 'value3');
       INSERT INTO tst.tableC VALUES(1, '2020-12-23', 'value4', 'value5', 'value6');
      INSERT INTO tst.tableC VALUES(1, '2020-12-24', 'value7', 'value8', 'value9');
    
     CREATE TABLE tst.tableB( id int, w1_ varchar(30),w2_ varchar(30),w3_ varchar(30));
      INSERT INTO tst.tableB VALUES(1, 'b1','b2', 'b3');
    
    SELECT b.id, ts, cl1_, cl2_, cl3_,b.*
      FROM tst.tableC c
      inner join tst.tableB b on b.id =c.id ;
    
    

    the select result is:

    I need the result looks like this when values in table b are dynamic :

    when always the column w1 in tabla B is the header of tabla C cl1, and so on
    always the column w2 in tabla B is the header of tabla C cl2...
    And again thank you so much for all the, very appreciative help o:)

  • moshegmosheg Vertica Employee Administrator
    \! vsql -Atc "SELECT 'SELECT c.id, c.ts, cl1_ as ' || max(w1_) || ', cl2_ as ' || max(w2_) || ', cl3_ as ' || max(w3_) || ' FROM tst.tableC c inner join tst.tableB b on b.id =c.id;' FROM tst.tableB;" | vsql -e
    
    SELECT c.id, c.ts, cl1_ as b1, cl2_ as b2, cl3_ as b3 FROM tst.tableC c inner join tst.tableB b on b.id =c.id;
     id |           ts           |   b1   |   b2   |   b3
    ----+------------------------+--------+--------+--------
      1 | 2020-12-24 00:00:00-05 | value7 | value8 | value9
      1 | 2020-12-22 00:00:00-05 | value1 | value2 | value3
      1 | 2020-12-23 00:00:00-05 | value4 | value5 | value6
    (3 rows)
    
  • relireli Vertica Customer

    @mosheg said:

    \! vsql -Atc "SELECT 'SELECT c.id, c.ts, cl1_ as ' || max(w1_) || ', cl2_ as ' || max(w2_) || ', cl3_ as ' || max(w3_) || ' FROM tst.tableC c inner join tst.tableB b on b.id =c.id;' FROM tst.tableB;" | vsql -e
    
    SELECT c.id, c.ts, cl1_ as b1, cl2_ as b2, cl3_ as b3 FROM tst.tableC c inner join tst.tableB b on b.id =c.id;
     id |           ts           |   b1   |   b2   |   b3
    ----+------------------------+--------+--------+--------
      1 | 2020-12-24 00:00:00-05 | value7 | value8 | value9
      1 | 2020-12-22 00:00:00-05 | value1 | value2 | value3
      1 | 2020-12-23 00:00:00-05 | value4 | value5 | value6
    (3 rows)
    

    SELECT c.id, c.ts, cl1_ as b1, cl2_ as b2, cl3_ as b3 FROM tst.tableC c inner join tst.tableB b on b.id =c.id;
    values in tableB dynamic for each select for id=1 the value is b1,b2,b3,but in id=2 the value is s1.s2,s3 for exmple.

  • Sudhakar_BSudhakar_B Vertica Customer ✭✭

    @reli
    Your requirement seems to be changing. @Ariel_Cary , @Jim_Knicely , and @mosheg suggested to you elegant solutions.

    • FLEX table
    • Dynamic VSQL
      What have you tried to solve your latest challenge? Please share your attempts.

    @mosheg provided a solution based on your example which included ONLY ONE ID !
    Do you want "different header values" for different ID ? That was not there in your example.
    Please share a complete workable example. That way experts in the forum can help better.
    If you really need different column HEADER for different ID (row value), may be we should visit the actual requirement and data model.

    Can you describe your REAL problem within the context of the DOMAIN ? With realistic business data? Why do you need different header values based on row values?
    What is the problem you are trying to solve?

    In Vertica almost any data manipulation can ultimately be achieved using UDXs, but do you really want that?
    Please help us help you.
    All responses in this forum are voluntary and let us respect their time.

  • relireli Vertica Customer

    hi,
    1.I pretty new in Vertica and the example in flex very much help me.
    2.I have 2 reports I need to provide, the first I get the answer as I write, the other with the time(ts) I get the answer from @mosheg
    3.my customer needs to run the query on the GUI tool so vsql is not relevant.
    4.Thank you very much for all the answers and sorry if the same one gets the impression that I don't respect his time, and thank you to Moshe for the solution he suggested!

  • vivekbaradvivekbarad Community Edition User
    edited December 2020

    @reli said:

    @Jim_Knicely said:
    Simple answer:

    dbadmin=> select MAX(w1) w1, MAX(w2) w2, MAX(w3) w3 from tst.f2_view;
     w1 | w2 | w3
    ----+----+----
     c1 | c2 | c3
    (1 row)
    

    But that's not very dynamic.

    Or you could change the insert:

    dbadmin=> truncate table tst.f2;
    TRUNCATE TABLE
    
    dbadmin=> insert into tst.f2 select mapaggregate(header, value) over() as __raw__ from (select b.w1_ header, a.c1_ value from tst.tableA a, tst.tableB b where a.id=b.id UNION ALL select b.w2_ header, a.c2_ value from tst.tableA a, tst.tableB b where a.id=b.id UNION ALL select b.w3_ header, a.c3_ value from tst.tableA a, tst.tableB b where a.id=b.id) t;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> COMMIT;
    COMMIT
    
    dbadmin=> select compute_flextable_keys_and_build_view('tst.f2');
                           compute_flextable_keys_and_build_view
    ------------------------------------------------------------------------------------
     Please see tst.f2_keys for updated keys
    The view tst.f2_view is ready for querying
    (1 row)
    
    dbadmin=> select * from tst.f2_view;
     w1 | w2 | w3
    ----+----+----
     c1 | c2 | c3 https://omegle.site/
    (1 row)
    

    Fyi .. This works too:

    dbadmin=> SELECT * FROM tst.tableA;
     id | c1_ | c2_ | c3_
    ----+-----+-----+-----
      1 | c1  | c2  | c3
    (1 row)
    
    dbadmin=> SELECT * FROM tst.tableB;
     id | w1_ | w2_ | w3_
    ----+-----+-----+-----
      1 | w1  | w2  | w3
    (1 row)
    
    dbadmin=>  \! vsql -Atc "SELECT 'SELECT' UNION ALL (SELECT '''' || a.c1_ || ''' AS \"' || b.w1_ || '\", ' || '''' || a.c2_ || ''' AS \"' || b.w2_|| '\", ' || '''' || a.c3_ || ''' AS \"' || b.w3_|| '\"' || DECODE(LEAD(a.c1_) OVER(ORDER BY 1), NULL, ';', ',') FROM tst.tableA a JOIN tst.tableB b USING (id) ORDER BY a.id)" | vsql
     w1 | w2 | w3
    ----+----+----
     c1 | c2 | c3
    (1 row)
    
    

    Thank you so much it's great!!! :blush:

    Very nice use of flex table @Ariel_Cary ! Excellent.

  • Interesting problem, in my opinion not solvable in SQL.
    But, you can generate SQL by another SQL, and then run it.
    Though, not possible to do in usual GUI applications.

Leave a Comment

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