Cross Tab in DB

[alyssa_sedai]

It would be awesome if there was a cross tab in DB option because right now I have to stream out millions of records to build a cross tab.

[fpinchon]

@helenzzhou @thejes Alteryx already offers a Transpose INDB tool, in case you missed it, it is hidden in the Laboratory tab: https://help.alteryx.com/2019.1/LockInTranspose.htm?Highlight=transpose While waiting for a Cross Tab INDB, one could leverage some built in SQL functions in the workflow through a Script tool. I am thinking about the PIVOT function available in: - SQL Server: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 - Snowflake: https://docs.snowflake.net/manuals/sql-reference/constructs/pivot.html I don't see it in Redshift though... Cheers, Frederic

[alyssa_sedai]

Hello @fpinchon 

That's awesome! I wonder when this was released because when wrote the original post early last year it didn't exist.  But I will have to test this out

[ExperTeece]

Yes please! We need these tools yesterday.

[Dan_Z]

Agreed! This would be an amazing option

[KylieF]

Thank you for your idea and feedback!

After thorough review with our product team, we have determined that we're unable to include this tool into the product at this time. Should this change however, we will be sure to update this idea back to Under Review.

[ngaskill1]

yes please

[chrissgtan]

Yes, me too! I need in-db cross tab to create dummy variables in machine learning.  Outside db would require streaming of huge dataset, which significantly slows down the query

[fpinchon]

Here is a post I put together that offers hacks, while Alteryx is working on a real INDB tools:

http://insightsthroughdata.com/cross-tab-and-transpose-indb-with-snowflake-using-pivot-and-unpivot/ 

it is not just for Snowflake, use the standard SQL script...

Cheers,

Frederic

[DFranklin]

Strong desire for this! Working with millions of records, streaming out to crosstab isn't an option nor efficient. An in-DB cross-tab would be work-life-changing!