Add Excel Expressions to a SQL Query Result

Question

I have a requirement where I need to execute a SQL Query which will be outputted to a .csv file. This will will then be opened in Excel. The data can then be manipulated in the spreadsheet and values automatically updated in computed columns (thanks to Excel expressions)

Here's an example:

Column A | Column B | Column C
1        | 2        | A+B
4        | 10       | A+B
         | 12       | A+B
0        | 1        | A+B

Column C is simple Column A + Column B, however I cannot simply write this in the query, as the purpose of the spreadsheet is to allow someone to edit columns A and B in Excel and then Column C will automatically be updated.

I've thought about including the Row Number in the query and writing Column C as "=A{N}+B{N}" where N is the row index.

Is it possible to achieve something like this?

Yeah, I wasn't sure how to get the row number. Looks like someone has answered that. I'll try it out and see how well it goes. :-) — Mike Eason
– Mike Eason, Commented Apr 23, 2015 at 12:35

John Bell · Accepted Answer · 2015-04-23 12:31:54Z

2

Sounds like you already have a plan:

SELECT [COLUMN A], [COLUMN B], 'A' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR) + '+B' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR) AS [COLUMN C]
FROM [TABLE1]

answered Apr 23, 2015 at 12:31

John Bell

2,3501 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Evaldas Buinauskas Over a year ago

I've just tried that myself - works like a charm. OP should remember that he has to deal with NULLs over there.

John Bell Over a year ago

Just thinking that myself. Nothing a good old ISO standard COALESCE won't sort out.

Evaldas Buinauskas Over a year ago

That's right. And another thought - maybe increasing ROW_NUMBER value by 1 in each row would be handy in case user wants to keep table headers.

Collectives™ on Stack Overflow

Add Excel Expressions to a SQL Query Result

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related