Export Multiple Dynamically Pivoted SQL Query Results to Excel

Question

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:\temp\tesxt.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.

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.

UPDATE:

Here is the @sqlCommandForBcp

set @sqlCommandForBcp = 'bcp "'+ @sqlForPivot + '" queryout "' + @filePath + @fileName + '" -S CLT00ZB1\SQLEXPRESS2019 -T -t , -d Repository -c'

This is a print out of the @sqlCommandForBcp which includes an example of the real pivot query. Each iteration through the Type list generates a new pivot query with different columns.

bcp "SELECT ProductID, KWGAID, ShortDesc, VendorID, Segment, Category, Type, SubType,[Airflow], [Amperage], [Bearings], [Cross Reference], [Diameter], [Enclosure], [Footnotes], [Frame], [Frequency], [Item], [Motor Type], [OEM], [Options], [Power], [Replacing], [Rotation], [RPM], [RPM-Range], [Series-Brand], [Shaft], [Speed], [Stack Size], [Voltage] 
            FROM
            (
                SELECT ''+ CONVERT(nvarchar(8), a.SeqNum) + '  |  ' + '' + a.TypeID + '' + '  |  ' + CONVERT(nvarchar(1), a.IncludeName) + '  |     ' + 
                         a.Value as Value, a.Name, p.KWGAID, p.SeqNum as ProductID, p.ShortDescription as ShortDesc, 
                            p.VendorID, p.VendorPartNumber, s.Name as Segment, c.Name as Category, 
                            t.Name as Type, st.Name as SubType

            FROM            Product p INNER JOIN
                                     Segment s ON p.SegmentID = s.ID INNER JOIN
                                     Category c ON p.CategoryID = c.ID INNER JOIN
                                     Type t ON p.TypeID = t.ID INNER JOIN
                                     Vendor v ON p.VendorID = v.ID INNER JOIN
                                     SubType st ON p.SubTypeID = st.ID INNER JOIN
                                     Attribute a ON p.SeqNum = a.ProductSeqNum INNER JOIN
                                     AttributeType at ON a.TypeID = at.ID

            Where v.ID = 132 AND c.Name like 'Blower Motors' 

            ) as PivotData
            PIVOT
            (
                Max(Value)
                For Name IN ([Airflow], [Amperage], [Bearings], [Cross Reference], [Diameter], [Enclosure], [Footnotes], [Frame], [Frequency], [Item], [Motor Type], [OEM], [Options], [Power], [Replacing], [Rotation], [RPM], [RPM-Range], [Series-Brand], [Shaft], [Speed], [Stack Size], [Voltage])
            ) as PivotResults Order by Segment, Category, Type, SubType, ShortDesc" queryout "C:\Temp\Blower Motors.csv" -S CLT00ZB1\SQLEXPRESS2019 -T -t , -d Repository -c

UPDATE:

Another step forward. Inserting the pivot query results into a temp global table and then selecting that table in the bcp command works! I am now getting results exporting to individual csv files; however, I'm seeing two issues. 1. the results do not include column headers; I have to have those and each pivot queries results will have different columns. 2. There are commas in the query results and that is throwing off the exported results. I read through the bcp documentation and didn't see a command switch to force column headers; also not sure how to handle the commas in the query results.

Update: I finally gave up on the tSQL approach with SSMSand starting writing a solution in another language. This was solely for a temporary cleanup project, not a long-term production solution. I’m using Access linked via ODBC and some VBA to handle looping through the list of Types and exporting the pivoted (CrossTab in Access) results to Excel. With VBA I have everything I need to create workbooks on the fly and insert worksheets, etc.

I learned a good bit about bcp and linked servers within SQL, but there just isn’t a very simple solution for what I’m wanting to do.

Not sure who should get the points here. Thanks to all for the input.

Answer 1

It will be better to build the file using another language. For example, there are a lot of techniques to generate the file using .net or you can use power-shell. The idea is to wrap your code in a stored procedure and call it from other language.

In the context of SQL Server, you can use BCP to generate a CSV file, which than can be consume by Excel.

You need to enable xp_cmdshell in order to be able to execute bcp command in the context of T-SQL:

 -- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Then, you simply to:

DECLARE       @sqlCommand   VARCHAR(1000)
DECLARE       @filePath     VARCHAR(100)
DECLARE       @fileName     VARCHAR(100)

SET    @filePath = 'C:\Temp\'
SET    @fileName = 'MyFile_' + CONVERT(VARCHAR, GETDATE(), 112) + '_' + CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.csv'

SET  @sqlCommand = 'bcp "SELECT 1 AS A, 2 AS B, 3 AS C UNION ALL SELECT 4, 5, 6" queryout "' +
                    @filePath + @fileName +
                    ' " -S RMVNSQL01\INST1 -T -t, -d smModel -c'

PRINT       @sqlCommand

EXEC   master..xp_cmdshell @sqlCommand

GO

Which will generate the following file MyFile_20200729_9_1.csv:

Also, if yo want to generate the excel file using SQL Server and you want to have separate sheets, you may want to use the SQL Server Reporting Server. The issue there is having your data dynamic, you will face difficulties to visualize it. It will be better just to ready it and perform the PIVOT there.

---

In order to add the headings insert a row at the beginning. Something like this:

SELECT 'ProductID', 'KWGAID', 'ShortDesc', 'VendorID', 'Segment', 'Category', 'Type', 'SubType', 'Airflow', 'Amperage', 'Bearings', 'Cross Reference', 'Diameter', 'Enclosure', 'Footnotes', 'Frame', 'Frequency', 'Item', 'Motor Type', 'OEM', 'Options', 'Power', 'Replacing', 'Rotation', 'RPM', 'RPM-Range', 'Series-Brand', 'Shaft', 'Speed', 'Stack Size', 'Voltage'
UNION ALL
...

Answer 2

If you can store a blank excel file on the server and use xp_cmdshell to copy it to a new location for each file, you can use:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=YES;IMEX=0;Database=c:\mitz\somex2.xlsx', 'Select * from [Sheet1$]')
SELECT * from mytable

This would insert data from your table into Sheet1. You need to install Ace Oledb and do a few tweaks if it's your 1st time using it.

If having a blank file is not a option, you could also create a new XLSX file using openrowset but it has a random sheet name. After creating it like that, you can use other selects to get the sheet name and then convert my upper insert statement into Dynamic SQL so that the worksheet name is dynamic. So the simple way would be to have a blank template.xlsx file and copy it for use in your excel file exports.

For setting up Ace OLEDB, see this link .

EDIT2: if you need variable column names:

• create a excel file with one row holding some text in it's first row. use as many cells as the max number of columns you will export. This will be your template file

• for each export, copy the template file using xp_cmdshell to a new file

• run this query to set column names (you will create the query with dynamic sql for your columns). set non=used columns to ' '(blank). The HDR=no means you update all rows, in this case 1st row which will be your header.

update OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=c:\mitz\blank.xlsx; HDR=no', 'Select * from [Sheet1$]')
set F1='mycol1', F2='MYCOL2'

• run this insert query. MYCOL1, MYCOL2 will also be dynamicly created. If column names need to have wierd names use [ ].

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=c:\mitz\blank.xlsx', 'Select MYCOL1, MYCOL2 from [Sheet1$]')
SELECT MYCOL1, MYCOL2 from ##some