0

I have some data which describes wider data sets. It's given in the format of three columns; fields, field type, notes. Each row is then one of the fields. What i want to do is pivot the first column so that the rows becomes columns into which i will load the data. I've been looking at the create table function using crosstab but the output just reorders my rows so far I've tried;

SELECT * 
FROM crosstab( 'select field, fieldtype, notes FROM 
databasestructure order by 1,2') 
AS final_result(field TEXT, fieldtype text,notes text);'
Cœur
  • 37,241
  • 25
  • 195
  • 267
mapping dom
  • 1,737
  • 4
  • 27
  • 50

1 Answers1

0

You need to install this extension:

CREATE EXTENSION tablefunc

then you need write your fieldtype names or aliases:

SELECT * 
FROM crosstab( 'select field, fieldtype, notes FROM 
databasestructure order by 1,2') 
AS final_result(field TEXT, fieldtype1 text,fieldtype2 text,fieldtype3 text);

you should determine first the number of your fieldtype in order the set your category column names. And be careful whatever your value type is this will be the type of your category columns. in this example your field is text so your fieldtype1..2..3 will have text values.

select distinct fieldtype
FROM databasestructure

Look at this crosstab example

Another useful link . It is explained in more details

In order give you an answer with desired output i need data from your table.

Community
  • 1
  • 1
light souls
  • 698
  • 1
  • 8
  • 17