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

[Srikanth_Baldwa]

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 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

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

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

Thanks,

Srikanth B

[sivamekala]

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.

[richyen]

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.

[Srikanth_Baldwa]

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 

[Srikanth_Baldwa]

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

[Srikanth_Baldwa]

Hi Team,

Can you please give your inputs on above mentioned request.

Thanks,

Srikanth B

[chaitalirs]

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.

[chaitalirs]

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.