Multi-statement table-valued function   with dynamic columns

Posted on 2005-04-22
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
    LVL 28

    Expert Comment

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

    Author Comment

    When you say temporary table you  mean user defined table?

    Author Comment

    Can you show like a rough example?
    LVL 28

    Expert Comment

    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.
    LVL 9

    Expert Comment

    >> 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

    Thanks guys for the help,

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


    Author Comment


      Please close this question.

    LVL 9

    Expert Comment

    Post a request to close and include a link here:

    Accepted Solution

    PAQed with points refunded (500)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now