-3

Fellow SOers,

Currently I am stuck with the following Problem.

Say we have table "data" and table "factor"

"data":

---------------------
|  col1   | col2     |
----------------------
|  foo    |  2       |
|  bar    |  3       |
----------------------

and table "factor" (the amount of rows is variable)

---------------------
|  name   | val     |
---------------------
|  f1    |  7       |
|  f2    |  8       |
|  f3    |  9       |
|  ...   |  ...     |
---------------------

and the following result should look like this: 

---------------------------------
|  col1  | f1  |  f2 |  f3 | ...|
---------------------------------
|  foo   |  14  | 16  | 18 | ...|
|  bar   |  21  | 24  | 27 | ...|
---------------------------------

So basically I want the column "col2" multiplicated with all the contents of "val" of table "factor" AND the content of column "name" should act as tableheader/columnname for the result.

We are using postgres 9.3 (upgrade to higher version may be possible), so an extended Search resulted in multiple possible solutions: using crosstab (though even with crosstab I was not able to figure this one out), using CTE "With" (preferred, but also no luck). Probably this may also be done with the correct use of array() and unnest().

Hence, any help is appreciated on how to achieve this (the less code, the better)

Tnx in advance!

BePe
  • 1
  • 2
    Why not do a cross join and let each entry in data be represented once for each row in factor, as the database is designed? – coreyward Jan 11 '17 at 17:31
  • 2
    Possible duplicate of [Create a pivot table with PostgreSQL](http://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgresql) – Gurwinder Singh Jan 11 '17 at 17:33
  • Tnx for the comments. When using simple cross join I still need to transpose the resultset, which basically leads again to "crosstab" (or I am misunderstanding something). And regarding crosstab itself: As mentioned I did not find a way to dynamically generate the columns i.e. crosstab ($magic) as (HOW_DO_I_GET_THE_COLS_IN_HERE?) – BePe Jan 11 '17 at 18:21

1 Answers1

0

This package seems to do what you want: https://github.com/hnsl/colpivot

Jeff
  • 1,122
  • 8
  • 7