Toad World® Forums

Export result set to Excel in pivot table format


#1

Is it possible to export a result set to Excel in a pivot table format? That is, with duplicate cell values in a single merged cell, and unique cell values in their own cells. I recently found this feature in MicroStrategy desktop client - the feature is called “merge” and it combines duplicate values as described above. At the moment I can link my query directly to excel with a data connection and then create an excel pivot table, but it would be nice to also be able to export this format directly.


#2

Ah ha, I see the send to pivot grid option - this appears to get me what I want, but now how do I get rid of the totals row and grand totals column? My result set is strictly informational, not quantitative.


#3

Are you simply trying to get rid of duplicate rows and merge values - if so then a pivot table is probably overkill. Simply group by and aggregate your data from the diagram part of query builder - but I think I may have misunderstood your question?


#4

Thanks for responding. Let me explain a little better how my result looks. Here’s a quick example: the data consists of products sold to customers, and then it contains some of the product attributes. So, the results are grouped by customer and product, and you might have 1 product where the attributes take up 5 unique rows, but the customer name and product name gets repeated 5 times. This is why I wanted the results to be exported in pivot table format.

The pivot grid works great for this, however, if you don’t have any meaures, you get a blank column called “Grand Total” in the export. I suppose I could create a column for some arbitrary measure, but I really didn’t want to do that.


#5

First time to see such a question, I never considered this before and imagine this to be impossible with the pivot table control. However, on the other hand, it is not quite difficult to find materials explaining about the gridview control exporting, including the guide of exporting gridview to excel.