• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

Multi-statement table-valued function with dynamic columns

In this following function example

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

return
end

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 :

systems
solutions
combo

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?

0
AnuPutcha
Asked:
AnuPutcha
  • 4
  • 2
  • 2
  • +1
1 Solution
 
rafranciscoCommented:
Why not make use of temporary tables and use dynamic SQL statement to create your temporary table that you will later populate.
0
 
AnuPutchaAuthor Commented:
When you say temporary table you  mean user defined table?
0
 
AnuPutchaAuthor Commented:
Can you show like a rough example?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
rafranciscoCommented:
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.
0
 
rherguthCommented:
>> 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
*/
CREATE FUNCTION fPriceAvg()
RETURNS TABLE RETURN (
      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.
0
 
AnuPutchaAuthor Commented:
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.

Anu.
0
 
AnuPutchaAuthor Commented:
Moderator,

  Please close this question.

Thanks,
Anu.
0
 
rherguthCommented:
Post a request to close and include a link here:
http://www.experts-exchange.com/Community_Support/CleanUp/
0
 
CetusMODCommented:
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now