3

I have a table which is called TrainingMatrix defined as below:

CREATE TABLE TrainingMatrix
(
  "ID" text NOT NULL,
  "TrainingName" text NOT NULL,
  "Institute" text,
  "ExpiryDate" date,
  CONSTRAINT "TrainingMatrix_pkey" PRIMARY KEY ("ID", "TrainingName", "ExpiryDate")
)

Some dummy data is as clarified in the below table: enter image description here

Editable and testable data can be found here (SQL Fiddle).

I am trying to write SQL statement which can regenerate the table to be as below: enter image description here

The TrainingName column in the original table TrainingMatrix can take any value, whenever a new value like Tx is added, the second intended table must consider this value as a new column as below:

enter image description here

I am using PostgreSQL 9.2.

Aan
  • 12,247
  • 36
  • 89
  • 150
  • 2
    Have a look at the `crosstab()` function in the [tablefunc](http://www.postgresql.org/docs/current/static/tablefunc.html) module –  Dec 30 '13 at 10:04
  • 1
    We very much prefer test data in a form we can work with, not as images. Try [SQLfiddle](http://sqlfiddle.com/#!12), or at least paste text, please. – Erwin Brandstetter Dec 30 '13 at 10:09
  • @ErwinBrandstetter Is there a way to paste the tables as a text with readable view? can you help please? – Aan Dec 30 '13 at 10:16
  • 1
    @ErwinBrandstetter Here is a ling to sqlfiddle: http://www.sqlfiddle.com/#!12/afce2/3 – krokodilko Dec 30 '13 at 10:42
  • @ErwinBrandstetter I have added sqlfiddle link. This is first time I know about sqlfidle :). – Aan Dec 30 '13 at 10:47

1 Answers1

1

Thanks for the fiddle, that's the best form to provide test data.

I cannot demonstrate the working solution on SQLfiddle, since one cannot install additional modules there. This may work in the future, discussion with Jake Feasel (the creator of the site) is ongoing whether to install a few more selected additional modules.

Anyway, this query does the job as requested (tested on Postgres 9.3):

SELECT * FROM crosstab(
       'SELECT "ID", "TrainingName", "ExpiryDate"
        FROM   trainingmatrix
        ORDER  BY 1,2'

      ,$$VALUES ('T1'::text), ('T2'), ('T3'), ('T4')$$)
AS ct ("Section" text, "T1" text, "T2" text, "T3" text, "T4" text);

You can find detailed explanation in this related answer:
PostgreSQL Crosstab Query

For a dynamic version, consider this related answer:
Dynamic alternative to pivot with CASE and GROUP BY

Completely dynamic is currently not possible. You need two steps:
1.) Build the statement itself (code at the second answer linked above).
2.) Execute it.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • What if a new different trainingName value is added to the orgianl table, will it be added dynamically to the new table? – Aan Dec 30 '13 at 13:00
  • @Aan: No. You would have to build the statement dynamically for such a requirement. A `SELECT` statement demands that columns are defined beforehand. (`SELECT *` derives this information from the system catalogs.) – Erwin Brandstetter Dec 30 '13 at 13:12
  • Actually this was the core of my question. How to add `trainingName` values dynamically to the new table. If you can edit your answer to cover this requirement I will be greatly thankful :) – Aan Dec 30 '13 at 13:18
  • @Aan: I added some more concerning dynamic crosstab queries. – Erwin Brandstetter Dec 30 '13 at 14:16
  • Thanks for editing I will go through your helpful links and also would like to ask you about double dollar signs `$$` in your SQL statement what does it means? – Aan Dec 30 '13 at 16:30
  • One more question, Why you wrote `'T1'::text` whereas `T2`,`T3` & `T4` without `::text`? – Aan Dec 30 '13 at 16:40
  • @Aan: 1) [**Dollar quoting**](http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING): That can also be found in the [first answer I link to](http://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905). 2) In a [**`VALUES`**](http://www.postgresql.org/docs/current/interactive/sql-values.html) expression, only the data types of the first row are considered. The rest cannot disagree. – Erwin Brandstetter Dec 30 '13 at 16:56
  • 1
    thanks for the help, i built my statement dynamically by c# and worked as a charm. – Aan Jan 01 '14 at 08:04