Dynamic Header Column When Exec Query

reli · December 2020

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?

Ariel_Cary · December 2020

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_Knicely · December 2020

@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

reli · December 2020

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?

Jim_Knicely · December 2020

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)

reli · December 2020

@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!!!

Sudhakar_B · December 2020

@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.

reli · December 2020

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_B · December 2020

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.

reli · December 2020

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_B · December 2020

Very nice use of flex table @Ariel_Cary ! Excellent.

reli · December 2020

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

reli · December 2020

Now I have a similar problem I would thank you if you help with the previous problem, you really saved me!!
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

mosheg · December 2020

\! 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)

reli · December 2020

@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_B · December 2020

@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.

reli · December 2020

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!

vivekbarad · 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!!!

Very nice use of flex table @Ariel_Cary ! Excellent.

Sergey_Cherepan_1 · January 2021

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.

Dynamic Header Column When Exec Query

Best Answers

Answers

Leave a Comment