How Sum values in dynamic table column.

Question

0.00/5 (No votes)

See more:

Can any one help me on this I have a Access Database on which I have tables with similar structure and columns name column a and column b. I can use the bellow query to get the the output running from vb.net.
table result
-------|--------

SQL

 SELECT "Table1" AS Table, SUM(a) - SUM(b) AS Result FROM table1
UNION
SELECT "Table2", SUM(a) - SUM(b) FROM table2
UNION
SELECT "Table3", SUM(a) - SUM(b) FROM table3

I would like to know is there any way to write a query without calling the table names in query? I need to create table or change some of table name at run time.

thanks

Posted 19-Jun-14 14:57pm

Member 10895647

Add a Solution

Comments

ZurdoDev 19-Jun-14 22:01pm

How will it know which table to select from if you don't tell it? I'm confused.

DamithSL 19-Jun-14 22:12pm

I think OP need to build SQL statement at run time without hard coding table names.

1 solution

Add a Solution

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

DamithSL · Answer 1 · 2014-06-19T16:09:00

Solution 1

Yes, you can do it :-)
create List with table names

C#

List<string> Tables = new List<string>();
Tables.Add("Table1");
Tables.Add("Table2");

And build your SQL statement like below

C#

string sql = string.Join( " UNION ", Tables.Select(t=> string.Format("SELECT '{0}' AS Table, SUM(a) - SUM(b) AS Result FROM {0}", t)));

Done :-)

Posted 19-Jun-14 16:09pm

DamithSL

v2

Comments

Member 10895647 20-Jun-14 18:56pm

I have list of tables names in a table called table_list .When I use create table statement passing text box input in query to create table , I am saving tables name in table_list using another insert query. In that case how to use the sql query ? any bit of info would be helpful

How Sum values in dynamic table column.

1 solution

Solution 1

Add your solution here

Preview 0

How Sum values in dynamic table column.

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us