Tilted Forum Project Discussion Community - View Single Post

Stompy · 04-15-2004, 11:43 AM

**UPDATE**

The 6 types I mentioned... turns out there can be an infinite number. The types are user defined.

Which brings the question... is there a way to dynamically add columns in a SQL statement, or would I just have to rebuild the stored procedure each time a type is added?

For example, if someone decided to add a type 4 to the transaction table I gave in my example, I'd have to take the existing stored procedure and, through code, add:

Code:

Type4 = (SELECT COUNT(*) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 4),

	Sum4 = (SELECT ISNULL(SUM(Amount), 0) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 4),

..to the existing code that selects those rows.

Also, another reason I can't really do the last group by method shown the post above this one (the one with the "SUM(DISTINCT") is because it will throw off paging. If the page size is 10 and there's 10 different types, page 1 would be only one record as opposed to.. 10 unique agent records.

04-15-2004, 11:43 AM	#2 (permalink)
Stompy Banned from being Banned Location: Donkey	UPDATE The 6 types I mentioned... turns out there can be an infinite number. The types are user defined. Which brings the question... is there a way to dynamically add columns in a SQL statement, or would I just have to rebuild the stored procedure each time a type is added? For example, if someone decided to add a type 4 to the transaction table I gave in my example, I'd have to take the existing stored procedure and, through code, add: Code: Type4 = (SELECT COUNT(*) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 4), Sum4 = (SELECT ISNULL(SUM(Amount), 0) FROM Transactions T WHERE AgentID = A.AgentID AND T.TransTypeID = 4), ..to the existing code that selects those rows. Also, another reason I can't really do the last group by method shown the post above this one (the one with the "SUM(DISTINCT") is because it will throw off paging. If the page size is 10 and there's 10 different types, page 1 would be only one record as opposed to.. 10 unique agent records. __________________ I love lamp.