Help : Unable to write the syntax for cross-tab function on a table consisting of 152 columns

[Srikanth_Baldwa]

Hi Team,

I need to write the syntax for cross-tab function on a table consisting of 152 columns.

can you please help me out how to write the syntax.

Note : I want to keep the six columns constant in 152 columns and do cross-tab on rest of the columns.

Thanks,

Srikanth B

[sachinfate]

Hi Srikant,

You can refer to the below link for the crosstab function syntax.

https://www.postgresql.org/docs/12/tablefunc.html

[Srikanth_Baldwa]

Hi Sachin,

Thanks for replying, i have gone through the URL which you mentioned above but when i trying for more columns it is not  giving the accurate results. I'm getting the below error.

Error : "DETAIL: The provided SQL must return 3 columns: rowid, category, and values"

Note : I have written the syntax in below format

select *  from crosstab ('select ColA, ColB, ColC , ColD, ColE, ColF, ColG
from col_table order by 1,2')
as ct (row_name character (500), category_1 character (500) , category_2 character (500) ).

Can you please help me out.

[sachinfate]

Hi Shrikant,

It looks like, you need to create custom wrappers for the general crosstab function. You can refer the F.35.1.3. crosstabN(text) section from the document.

https://www.postgresql.org/docs/11/tablefunc.html

[Srikanth_Baldwa]

Hi Sachin,

I was able to create the function for cross-tab for N columns but unable to execute the syntax can you please look into my query.

ERROR: a column definition list is only allowed for functions returning "record"

Query : 

select *  from crosstabN ('select ColA, ColB, ColC , ColD, ColE, ColF, ColG
from col_table order by 1,2')
as ct (row_name character (500), category_1 character (500) , category_2 character (500) ).

For refrence : There are A,B,C,D,E,F,G,H,I,J,K,L are the columns in the table i want to crosstab like A,B,C,D,X,Y

in X --> E,F,G,H,I,J,K,L should come and in Y values related to E,F,G,H,I,J,K,L 

Thanks,

Srikanth

[Srikanth_Baldwa]

Hi Sachin,

Below table is my input table.

How to write the crosstab syntax for this scenario.

Please help me out.

Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H
111	Air	Oxygen	Carbon	hydrogen	helium	nitrogen	argon
222	Air	argon	Oxygen	Carbon	hydrogen	helium	nitrogen
333	Air	Carbon	hydrogen	helium	nitrogen	argon	Oxygen
444	Air	nitrogen	argon	Oxygen	Carbon	hydrogen	helium
555	Air	helium	nitrogen	argon	Oxygen	Carbon	hydrogen
666	Air	hydrogen	helium	nitrogen	argon	Oxygen	Carbon

output table 

Col A	Col B	Col Name	Col Value
111	Air	Col C	Oxygen
111	Air	Col C	Carbon
111	Air	Col C	hydrogen
111	Air	Col C	helium
111	Air	Col C	nitrogen
111	Air	Col C	argon
222	Air	Col D	argon
222	Air	Col D	Oxygen
222	Air	Col D	Carbon
222	Air	Col D	hydrogen
222	Air	Col D	helium
222	Air	Col D	nitrogen
333	Air	Col E	Carbon
333	Air	Col E	hydrogen
333	Air	Col E	helium
333	Air	Col E	nitrogen
333	Air	Col E	argon
333	Air	Col E	Oxygen
444	Air	Col F
555	Air	Col G
666	Air	Col H

same for the 444, 555, 666.

Thanks,

Srikanth B

[Srikanth_Baldwa]

Hi Team,

Can you please give your inputs on above mentioned request.

Thanks,

Srikanth B