sql - How to create table where columns in first table are fields in another table? -

table_1

  t1_id  name  -------------   1      mark   2     stieve 

table_2

t2_id   month  amt   t1_id ----------------------------  1      jan    200     1  2      feb    400     1  3      jan    500     2 

expected output

view_table_3

 t1_id   name    jan    feb  -----------------------    1      mark   200   400    2     stieve  500    0 

in view_table_3 need make columns jan , feb fields in table_2 , respective amt.

please how can output?

in postgresql can install tablefunc extension:

create extension tablefunc; 

in extension find crosstab(text, text) function. ugly function work (putting mildly), want:

select * crosstab(     'select t1_id, name, month, coalesce(amount, 0) table_1 join table_2 using (t1_id)',     'values (''jan''), (''feb''), (''mar''), (''apr''), (''may''), (''jun''), (''jul''), (''aug''), (''sep''), (''oct''), (''nov''), (''dec'')'   ) (     id int, name varchar,     jan int, feb int, mar int, apr int, may int, jun int,     jul int, aug int, sep int, oct int, nov int, dec int ); 

the first parameter specifies data cross-tabulated, in specific format (check documentation), second parameter specifies categories. assuming here months of year.

if can have multiple records same person in single month, can sum coalesce(amount, 0) in first parameter, , add group 1, 2, 3 clause.

finally, can wrap in view:

create view view_table_3   select * crosstab(...