• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

How to insert a coulmn in Excel that is resulted from calculated columns in sql database

I am importing table data from SQl database in Excel by query.. however, I would like to insert additional columns in excel which are resulted from a formula done to already existing columns in the database. For instance, in SQL database I have Campaign table that consist of (AccountID, Campaign, Cost, Clicks and impressions) these columns are imported successfully to Excel. I would like to add to Excel table another column (CTR) which is equals (Clicks/impressions)
is there any way that allow me to do that?

Thank you
0
Rawan Jambi
Asked:
Rawan Jambi
1 Solution
 
RayData AnalystCommented:
You can do it one of two ways.  The easiest, add an item to the select query that like
      ,clicks/impressions as 'CTR'
That will put the results in with the query results.  

Option B, if you're using a query table within the excel, is to simply add a formula to the column next to the last column of your query results.  This link gives a pretty good detail on doing that so I won't 'reinvent the wheel here'.
http://www.agentjim.com/mvp/excel/2011Relational22Queries.html
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You can add extra column to sql

insert into OPENROWSET('Microsoft.Ace.OLEDB.12.0','Excel 12.0;Database=D:\testing.xlsx;', 
    'SELECT * FROM [Sheet2$]') select *,yourcolumn  as CTR from Comb

Open in new window

0
 
Don ThomsonCommented:
What version of Excel are you working with?
I personally would import the SQL data into Access - then run a query to add the formula and create a new table (or modify the existing table)  and then if you need to, export it to an Excel format.

You could of course just do it directly in Excel using a Macro (activated with either a keystroke or via a button)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now