How do I Unpivot data?

Toad Data Point & Toad Intelligence Central
1

How do I unpivot data? Do I have to just do a series of appends?

2

Welcome to the Toad Data Point community!

Let's take it from the top... how did you create the pivot in the first place?
If you created it from a results grid (a query in the Editor or Query Builder), then you should still have the original results from which the pivot is made.

What is it you're trying to accomplish in "unpivoting" the data?

3

Hi Gary. The data is coming from an excel file that I don't create. The file has a list of stores and each column is a different product with sales amounts. Ultimately, I will need to union this with files that have other products in it. I want to unpivot the data to get all product's sales into the same column.

4

Short of doing something like the snap below...

image

.. then I'm not sure of a way to undo a pivot if that's how the spreadsheet was created (e.g. with only the pivoted data).

Important thing to note, however, is that you can join the Excel pivot with other data sources within the Query Builder, if you have the Professional Edition, that is. That might help you get the detail sales records if you know where they reside... Hope that helps.

5

thanks for your help. I'm amazed that such a simple operation can't be done. I've tried to use TOAD several times over the years and every time I get stalled b/c TOAD can't do something this basic.

6

You mean Excel... from your description, this is not a Toad issue... unless I'm not understanding your scenario completely...

7

???? this has nothing to do with excel. I'm trying to get toad to unpivot data and apparently it can't.

8

I'm not talking about Toad consuming a pivot table created by excel. I have a table of data in an excel file that is pivoted. I want to undo that pivot so that the data is vertical instead of horizontal. Can Toad do that?

9

Hi Matt,
I'm really not sure what you are trying to do. Are you working with an Excel type connection and trying to extract data table from an Excel Pivot file? Is it possible to import the table only or work with the data from the Excel table? Can you send us a sample file to test it?

10

Di Dan. My Data is horizontal and looks like this like this:
Store ID, Date, Product1, Product2, Product3
Store 1, 1/1/20, 10,20,30

I need the data to be vertical, like this:
Store ID, Date, Product Type, Sales Amount
Store1, 1/1/20, Product1, 10
Store1, 1/1/20, Product2, 20
Store1, 1/1/20, Product3, 30

My Data source is in an excel file but it could just as easily be a CVS, text file or the output of another query. The point is, I need Toad to unpivot the data. Can Toad do that?