0

I have a table with data

id | location  | data | 
-----------------------
0  | point 0,1 | "Dachshund"
1  | point 0,0 | "Dog Breeds"
2  | point 1,0 | "Quantity"
3  | point 0,1 | "Rhodesian"
4  | point 1,1 | "2"
5  | point 1,2 | "1"

Am looking to translate it into:

0 as text     | 1 as text
--------------------------
"Dog Breeds"  | "Quantity"
"Dachshund"   | "2"
"Rhodesian"   | "1"

Is there a way to dynamically do this in PostgresSQL? The data points could expand an arbitrary amount of cells and columns in any direction. The resulting column names should always be of type text and can just be named by the y coordinate of the location given in the first table.

I have tried to accomplish this with Postgres tablefunc and crosstab however that function appears to require the columns to be predefined.

user1805218
  • 15
  • 4
  • 1
    I would rather suggest a two-dimensional array. Do you really need to create a table? If yes, you can certainly do it with dynamic pl/pgsql. – Bergi Nov 11 '21 at 00:23
  • What are the data types? Is that really a job for an SQL statement? – Laurenz Albe Nov 11 '21 at 02:28
  • Yes, the result should be a table. The idea is to be able to generate a table (or view) that can easily be queried against. – user1805218 Nov 11 '21 at 03:32

1 Answers1

1

Try this :

CREATE OR REPLACE VIEW view_name AS
SELECT a.data AS text_0, b.data AS text_1
  FROM your_table AS a
 INNER JOIN your_table AS b
    ON a.location[1] = b.location[1]  -- points a.location and b.location have same y
   AND a.location <> b.location       -- points a.location and b.location are differents
 WHERE a.location[0] = 0.0
   AND b.location[0] = 1.0 ;

btw, in your example, should the "Rhodesian" data be associated to the position 'point 0,2' instead of 'point 0,1' ?

Edouard
  • 6,577
  • 1
  • 9
  • 20
  • "*The data points could expand an arbitrary amount of cells and columns in any direction*" – Bergi Nov 12 '21 at 17:43
  • OK, fine. In this case, can you explain the logic to translate the initial data in the table into the final expected view ? What is the rule to select and compute two rows of the table into one row of the view ? – Edouard Nov 12 '21 at 18:12