- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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 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
Archived Discussions
Effective March 31st, we will no longer engage on PostgresRocks.
How to engage with us further?
- Thought Leadership: EDB Blogs
- Tips and Tricks: Postgres Tutorials
- Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket
- Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: Help : How to write the Cross-tab or Transpose query syntax
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.