cancel
Showing results for 
Search instead for 
Did you mean: 

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

Highlighted
Level 2 Adventurer

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

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

 

 

 

 

6 REPLIES 6
EDB Team Member

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

Hi Srikant,

 

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

 

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

Highlighted
Level 2 Adventurer

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

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.

Highlighted
EDB Team Member

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

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

Highlighted
Level 2 Adventurer

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

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

 

 

Highlighted
Level 2 Adventurer

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

Hi Sachin,

 

Below table is my input table.

How to write the crosstab syntax for this scenario.

Please help me out.

 

Col ACol BCol CCol DCol ECol FCol GCol H
111AirOxygenCarbonhydrogenheliumnitrogenargon
222AirargonOxygenCarbonhydrogenheliumnitrogen
333AirCarbonhydrogenheliumnitrogenargonOxygen
444AirnitrogenargonOxygenCarbonhydrogenhelium
555AirheliumnitrogenargonOxygenCarbonhydrogen
666AirhydrogenheliumnitrogenargonOxygenCarbon

 

output table 

Col ACol BCol NameCol Value
111AirCol COxygen
111AirCol CCarbon
111AirCol Chydrogen
111AirCol Chelium
111AirCol Cnitrogen
111AirCol Cargon
222AirCol Dargon
222AirCol DOxygen
222AirCol DCarbon
222AirCol Dhydrogen
222AirCol Dhelium
222AirCol Dnitrogen
333AirCol ECarbon
333AirCol Ehydrogen
333AirCol Ehelium
333AirCol Enitrogen
333AirCol Eargon
333AirCol EOxygen
444AirCol F 
555AirCol G 
666AirCol H 

 

same for the 444, 555, 666.

 

Thanks,

Srikanth B

Highlighted
Level 2 Adventurer

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

Hi Team,

 

Can you please give your inputs on above mentioned request.

 

Thanks,

Srikanth B