#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2010
    Posts
    20
    Rep Power
    0

    sum of dynamic tables column


    I am trying to create dynamic tables
    with two columns such as
    name,amount by passing table
    names from text box .
    sql " "
    I want to display output like this
    table name| total
    table1 | 100
    table2 | 200

    table1
    name | amount
    a | 40
    b | 60

    table2
    name | amount
    a | 150
    b | 50

    I can create tables, retrive table
    names. I wd like to know is it possible
    to sum a column of the dynamic
    tables in sql server 08 ?
    thanks
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,665
    Rep Power
    283
    what is "dynamic tables"?
    in your example it is static tables.
    How you know which table to pick for calculating total?
    Any rules?
    Try something like that
    Code:
    declare @sql varchar(max)
    
    SELECT sql = 'select sum(' + COLUMN_NAME + ') as total,  table_nm = ''' + TABLE_NAME +
    		''' from ' + TABLE_NAME 
    into #tmp		
    	FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '%amount%'
    
    SELECT @sql = COALESCE(@sql + ' union ', '') + sql 
    FROM #tmp
    
    exec @sql
    this code for all tables in database which has column amount

    Comments on this post

    • Will-O-The-Wisp agrees : Thanks for helping out here!
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2010
    Posts
    20
    Rep Power
    0
    I am new to sql server .I have recently downloaded Sqlserver 2008 and connected from vb.net .
    I am trying to create tables by passing table name from text box
    Code:
    CREATE TABLE " & TblName & "( [Name] TEXT(10), [Amount] TEXT(6))]


    I can retrieve table names using the below query
    Code:
      SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    I also have tried the query but it displays a empty column
    Code:
    "SELECT 'SELECT ISNULL(SUM('+COLUMN_NAME + '),0) AS a FROM ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'amount'"


    Thank you so much for your reply .I am slowly working on the new query
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,665
    Rep Power
    283
    The main question is why you creating table from application I do not think it is good idea...
    If all table you planning to create has the same structure, why not use the one table for all of them?
    just create table with right indexes as on example below
    Code:
    create table yourTableName
    (
          [name] varchar(10) not null,
          amount varchar(6), not null default '0',
          [userTableName] varchar(20) not null,
          PRIMARY KEY ( [userTableName], [name] )
    )
    and you never need to use INFORMATION_SCHEMA table....
    just
    Code:
    select sum(amount), userTableName
    from yourTableName
    group by userTableName

Similar Threads

  1. Dynamic Column Data
    By Vaulcul in forum Microsoft Access Help
    Replies: 3
    Last Post: July 25th, 2012, 09:08 PM
  2. Replies: 15
    Last Post: February 7th, 2012, 04:14 PM
  3. How to add dynamic column
    By lyealain in forum HTML, JavaScript And CSS Help
    Replies: 7
    Last Post: July 11th, 2007, 10:28 AM
  4. Creating Dynamic tables in dreamweaver to display tables
    By anthony9000 in forum ASP Development
    Replies: 2
    Last Post: March 2nd, 2006, 10:50 AM
  5. Replies: 1
    Last Post: July 30th, 2004, 09:04 AM

IMN logo majestic logo threadwatch logo seochat tools logo