Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Multi-statement table-valued function   with dynamic columns

Posted on 2005-04-22
Medium Priority
Last Modified: 2008-01-09
In this following function example

create function fpriceAvg()
return @price Table
( code char(10),
effectivedate datetime)


Is there a way to declare dynamic table columns? for example in the example above code and effectivedate are the only 2 columns in the @price Table.

But  my requirement is, based on what values a select statement returns, I use them as columns for a dynamic table.

eg: select distinct elements from Registration

output :


I need to save them first ( may be using a cursor ) then I want to build a table with each row as my column heading.

eg :
                          systems              solutions           combo

company 1             10                        2                      4
company 2              4                         7                       3

Which means I had 10 people from company1 attending systems event, 2 people from company1 attended soultions event .....

So, my problem is in multi-valued user defined table, the table column names are defined once and for all, can they be dynamic like in my case ?

Is there a way this whole thing be done in sql itself ( SQL SERVER 2000)
  without running back and forth between ASP.NET Pages and sql statements?

Question by:AnuPutcha
  • 4
  • 2
  • 2
  • +1
LVL 28

Expert Comment

ID: 13845606
Why not make use of temporary tables and use dynamic SQL statement to create your temporary table that you will later populate.

Author Comment

ID: 13846127
When you say temporary table you  mean user defined table?

Author Comment

ID: 13846139
Can you show like a rough example?
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 28

Expert Comment

ID: 13846376
Temporary tables are tables that begin with the pound (#) sign.  Tables that begin with one # are local temporary tables while tables that begin with two #s are global temporary tables.

You can loop through your distinct values and create your temporary table just like any other table:

SET @SQL = 'CREATE TABLE #TempTable ('
For each element in Registration
   SET @SQL = @SQL + ElementName + ' INT, '

SET @SQL = @SQL + ')'
EXEC (@SQL) -- This will create your temporary table that you can then populate and select.

Local temporary tables get dropped after each session.  Global temporary tables need to be dropped so that the next time your stored procedure runs, it will not fail because the table already exists.

Expert Comment

ID: 13847194
>> Is there a way to declare dynamic table columns? (presumably, you're referring to the function return)

Yes, if you can do without a BEGIN...END block.
Rowset Return Function
      SELECT code, effectivedate
      FROM xyz
      WHERE abc=123

But if you want to do the whole thing in SQL, you'll need to create a Stored Procedure that uses a global (##) temp table.  The local temp table, by default, will only be available within the context of the EXEC() (in the example given above), which is why you'll need a global temp to do this.  I created an inventory system using this methodology once.  It became obvious that the customer didn't know all of the attributes and item classes that required tracking at the time the system was being built.  So, I built a flexible set of tables where new attributes (color, serial number, texture, etc.) could be defined for every different type and class of inventory item.  In this case, there was no "item" table at all.  To get the item rows, the application made one call to an SP to fetch the rows for a type, class, or single item.  That SP made a call to a getDDL() function that returned the DDL that would be EXEC'd into a global temp table.  Then the SP would fill that temp table with the data from the various type, class, attribute tables and return the results to the app and DROP the table.  I think the SP was maybe 150 lines and the 3 functions, maybe 100 lines of TSQL each.

The app's OLTP performance was actually very good (probably because SQL Server like long and short tables structures).  The OLAP performance was not good, but the customer was fine with a separate OLAP database and one-day old data for reporting.

Author Comment

ID: 13891158
Thanks guys for the help,

I could solve the problem by splitting the sql code in asp.net. But pivot table I generate takes like 2 minutes to build.


Author Comment

ID: 13998031

  Please close this question.


Expert Comment

ID: 14000857
Post a request to close and include a link here:

Accepted Solution

CetusMOD earned 0 total points
ID: 15651110
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question