- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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
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 : 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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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 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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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