cancel
Showing results for 
Search instead for 
Did you mean: 

Help : How to write the Cross-tab or Transpose query syntax

Level 2 Adventurer

Help : How to write the Cross-tab or Transpose query syntax

Hi Team,

 

Can you please help me how to write SQL query or syntax for cross-tab function in Postgresql.

 

NOTE : want to do the transpose of columns by keeping the two three columns constant.

 

Below is my input table :

 

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

 

Below should be my 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 

 

Values willl be repeated as same for 444,555,666.

 

Thanks,

Srikanth B

6 REPLIES 6
EDB Team Member

Re: Help : How to write the Cross-tab or Transpose query syntax

Hi Srikanth, 

 

Hope you are doing good. 

 

We kindly request you to please refer following link for query formation for cross-tab function. 

 

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

 

Pleaes let us know if you face any issue. 

 

Regards

Siva.

Moderator

Re: Help : How to write the Cross-tab or Transpose query syntax

Hi Srikanth,

 

Unfortunately, crosstab doesn't dynamically aggregate many columns into one, as it seems that you are trying to do (with the added difficulty of providing a label ("Col Name") identifying its source column).  You'll need to do some combination of row_to_json (to capture column names) and json_agg (to combine the values together).  Unless you absolutely need to do it on the database side, it might actually be easier to aggregate the columns using software/code.

Level 2 Adventurer

Re: Help : How to write the Cross-tab or Transpose query syntax

Hi @richyen ,

 

Can you please help me out how to write the Software/ Code on database side to perform this action or with combination of row_to_json (to capture column names) and json_agg (to combine the values together).

 

If you can give me more input from yourside, it might be really helpful for building the Application.

 

Thanks,

Srikanth B 

 

 

Level 2 Adventurer

Re: Help : How to write the Cross-tab or Transpose query syntax

Hi @sivamekala,

 

I was following the same URL which you mentioned, by doing the custom wrappers but unable to execute the syntax.

 

If you can give me more input that might be really helpful for me.

 

Thanks,

Srikanth

Level 2 Adventurer

Re: Help : How to write the Cross-tab or Transpose query syntax

Hi Team,

 

Can you please give your inputs on above mentioned request.

 

 

Thanks,

Srikanth B

EDB Team Member

Re: Help : How to write the Cross-tab or Transpose query syntax

HiSrikanth_Baldwa,

 

To avoid delay in responses, we request you to raise a case to "support@enterprisedb.com" with the subscribed user.

 

Thank you for being a part of the PostgresRocks community.