#StackBounty: #sql-server #tsql #pivot #ssms #export-to-excel Export Multiple Dynamically Pivoted SQL Query Results to Excel

Bounty: 100

I have a batch query that I execute from SQL Server Management Studio (v18.5) that has multiple dynamic pieces and generates multiple query results that I would like to export to Excel automatically from the SSMS console, if possible.

In the batch query, I first select a unique set values from 1 column in a table, and I iterate through that list to build a dynamic pivot query using the value from the list. Each pivot query will have a different set of results for each value.

For example: unique list that I loop through:

Type
-----
Fan
Compressor
Belt
Motor
Filter

The pivot query results for Fan will have a unique set of columns that are different from the pivot query results for Compressor.

Fan pivot columns

FanID, Speed, Weight, Blade Size, RPM

Compressor pivot columns

CompressorID, HP, Voltage, Amps, Height, 

Each time I loop through the Type list, I would like to export the pivoted query results to an Excel file. Ideally, I would like to export to 1 workbook with each Type’s pivoted query results having its own worksheet. Since each pivoted query result will have a difference set of columns, I cannot compile all of the query results into 1 table and then export to Excel (or csv). Trying to avoid having multiple files, one for each type.

UPDATE:

I have added the following to my batch query:

INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0; Database=C:temptesxt.xlsx;'',''SELECT * FROM [Sheet1$]'')

But I’m getting the following error:

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

I ran these two commands prior to attempting the code above:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

UPDATE:
I was hoping there was a down and dirty export option that would eliminate the need to write an application.

I tried the BCP approach, but I was unable to get it to work. I changed the server name to mine and the database name to mine; no luck. The @sql variable is my dynamic pivot query. Tried different bcp command parameters with no luck.

enter image description here

enter image description here

UPDATE:
Getting a little closer; I replaced @sql from the bcp command above with a simple select query and the command executed, creating a new .csv file for each "Type" in the type list; there must be something in the complex dynamic pivot @sql string that bcp doesn’t like. Regardless, I know the underlying plumbing of having bcp export query results to a csv file is working.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.