Do a select on a range in excel and outputting that in another worksheet
It's possible to use Excel as a ODBC source and do a query on data in a worksheet.
Here's what I've done.
I've imported data into Excel from a SQL-server database into a worksheet. I use aselect
statement that connects to SQl_server and dumps the data into sheet1
.
开发者_如何转开发
The initial select if long and complicated, and I manipulate it using VBA to make the query dynamic.
For this reason I do not want to mess around with the original query to enclose it in aSELECT a,b,c, sum(debit) as debit, sum(credit) as credit, accountnumber FROM (
SELECT lost_of_fields
FROM lots_of_tables_with_lots_of_joins
WHERE couple_of_conditions
ORDER BY few_fields ) AS subselect
) GROUP BY subselect.accountnumber
I don't want to do this because SQL-server complains about selecting lots of fields, whilst only doing a GROUP BY
on one of them.
Question
Can I do aSELECT a,b,c, sum(debit) as debit, sum(credit) as credit, accountnumber
FROM named_range_in_excel
GROUP BY accountnumber
And dump the output into a sheet in the same Excel workbook?
If I can do this without using VBA, that would be cool, if VBA is needed that's OK too.You might try using an "External Data" reference, using a query from the same workbook and the query editor to form the SELECT statement.
You don't say what version of Excel (the details vary a bit, but the capability is much the same across versions)
Eg in 2010: Data tab, Get External Data, From Other Sources, From Microsoft Query, ExcelFiles.
UPDATE: added screenshot. But, SQL does complain the group by as you said. I'm not a SQL expert. others may be able shed some light...
An example of the expected output might help.
精彩评论