0

i would like convert data from Table1 as you can see on first picture on data in Pivot_table. Below you can see script in Mysql that is for dynamic pivot table, but i would like to know similar or suitable solution for this in Postgresql, if it is possible?

Table1:
PK        Name     Subject     Grade**
-------------------------------------
1         Bob       Math        A
2         Bob       History     B
3         Bob       Language    C
4         Bob       Biology     D
5         Sue       History     C
6         Sue       Math        A
7         Sue       Music       A
8         Sue       Geography   C


Pivot_table:
Subject     Bob     Sue
-------------------------
Math        A        A
History     B        C
Language    C 
Biology     D
Music                A
Geography            C

Script in Mysql:

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN name = ''', name,
                ''' THEN grade END) `', name, '`'))
  INTO @sql
  FROM table1;

SET @sql = CONCAT('SELECT subject, ', @sql, ' 
                     FROM table1 
                    GROUP BY subject');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Thank you very much

Bushwacka
  • 865
  • 4
  • 12
  • 22
  • Why, why, why oh WHY are you trying to do this in your database layer? Why not perform such an operation in the presentation layer of your application? – eggyal Apr 20 '14 at 20:33
  • Why is it better to do this in application? Could you write me some advantages and disadvantages? thank you – Bushwacka Apr 20 '14 at 20:50
  • I suppose, fundamentally, you're dealing with a presentational issue. You're happy with the data that is returned, you just want users to interface with it in some other presentational format. But this isn't really a task for the database, since the database doesn't provide the UI: it's really a task for your application to transform the data into a form suitable for your desired user interaction. – eggyal Apr 20 '14 at 20:57
  • Ok, but in my case i would like to execute this sql script and export it into csv from my database GUI, so i would rather prefer this in sql, but i don't know if this is possible in postgresql. This script is only example of my real solution – Bushwacka Apr 20 '14 at 21:07
  • 1
    See [PostgreSQL Crosstab Query](http://stackoverflow.com/a/11751905). – eggyal Apr 20 '14 at 21:11

0 Answers0