How Do I Export Sql Query Result Into Pivot Table Automatically

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi
I want to save my sql query result as excel(pivot) file.
I use from below code:

SQL

insert into OPENROWSET
('Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0 Xml;Database=C:\Test.xlsx;HDR=YES;','SELECT * FROM [sheet1$]')
select * from SalesItem

Before this, every time i need to create excel file and add columns in it.which works fine but creates normal Excel file.
But I need to create excel pivot file automatically in a specified folder and at the beginning of every month(separate file for every month).How can i pass month and year for my query.
I am using SQL server 2008 R2 32-bit and Office 2010 standard.

Posted 5-Sep-15 14:44pm

Member 11584553

Updated 5-Sep-15 22:43pm

Suvendu Shekhar Giri

v2

Add a Solution

Comments

Tomas Takac 6-Sep-15 8:24am

Export the data to one tab and have the pivot in a second tab referencing the data in first tab.

Member 11584553 6-Sep-15 21:45pm

Thank you Tomas Takac. Actually creation of Excel file and conversion into pivot table should be done automatically at the beginning of every month. How should i do that

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chairborne82 · Answer 1 · 2015-09-06T17:48:00

Solution 1

Create a table in SQL server that holds your pivoted data and export that to excel.

Posted 6-Sep-15 17:48pm

chairborne82

Comments

Member 11584553 7-Sep-15 0:11am

Actually SalesItem is a view name(which i used above) . In that view i joined 3 different tables. when i execute view i get 8 Columns.That data should be automatically exported as pivot table every month.

Rakesh Meel · Answer 2 · 2015-09-06T18:58:00

Solution 2

This Will Help you....http://stackoverflow.com/questions/7674786/mysql-pivot-table[^]

Posted 6-Sep-15 18:58pm

Rakesh Meel

Comments

Member 11584553 8-Sep-15 22:49pm

I came out with a different approach...

I need to write a batch file that copies a file to existing folder and renames it with current month and year. I used the below code

At the moment, my batch file consists of only this command:

**@ECHO OFF
copy /y D:\SalesMixExp\Sales_Mix_v1.2_082015.xlsx D:\SalesMixExp\Sales_Mix_v1.2_092015.xlsx**

It works fine. But every time i need to change the newly created filename with current month and year. i.e for ex: 092015 in my filename is **sep2015** and for next month it should be automatically renamed as **Sales_Mix_v1.2_102015.xlsx**...Please help me in resolving my problem