http://www.windowsitpro.com/authors/author/author/5777598/rsscomment/5777598en-USSun, 27 May 2012 07:35:55 GMTSun, 27 May 2012 07:35:55 GMThttp://www.windowsitpro.com/article/performance/inline-conditional-aggregation-one-for-all#commentsAnchorMon, 21 Aug 2006 12:12:04 GMT
How do I add functionality to automatically compute the date range i.e. SUM (CASE WHEN trans_date BETWEEN GETDATE() and GETDATE()-365 THEN vid.QTY ELSE 0 END)/12]]>JACOBMon, 21 Aug 2006 12:12:04 GMThttp://www.windowsitpro.com/article/performance/inline-conditional-aggregation-one-for-all#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/temporary-tables-local-vs-global#commentsAnchorTue, 01 Feb 2005 02:05:59 GMT
abcxyz]]>Anonymous User Tue, 01 Feb 2005 02:05:59 GMThttp://www.windowsitpro.com/article/tsql3/temporary-tables-local-vs-global#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorWed, 27 Oct 2004 15:18:53 GMT
http://www.codeproject.com/cs/database/Trees_in_SQL_databases.asp]]>Anonymous User Wed, 27 Oct 2004 15:18:53 GMThttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorWed, 14 Apr 2004 16:10:06 GMT
Great!!!]]>LogeswariWed, 14 Apr 2004 16:10:06 GMThttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorMon, 16 Dec 2002 07:42:34 GMT
On Friday, Dec 13 2002, I posted a comment, about another solution to the sample posted with the article "Using T-SQL to Generate a Resultset in Tree Form". The solution, I sent to you, worked fine because casualty, I did not use any ORDER BY clause in the select statement to assure the result’s order. Here are two revisited versions, the first one, use "ORDER BY activity_code" because the way data was enter (A1, A2, A2.1, A2.2, etc.). The second one use a function that return the concatenation of parents and child’s key in reverse order (first parent, second, third, ..., child’s key) to assure the order of the result. This solution has a lot of cons but if the concatenation of the keys does not exceeds 8000 characters, then this solution fit well. It does not perform very well in terms of time because the HierarchyLevel and HierarchyOrder is calculated for each node in the tree. I created two functions, one (HierarchyLevel) returns the level of the node in the tree, and the other (HierarchyOrder) returns the concatenation of the parents and child key, to be used in the ORDER BY clause. CREATE TABLE project_activity_schedule ( activity_code VARCHAR(10) NOT NULL PRIMARY KEY, activity_description VARCHAR(255) NOT NULL, planned_start_date datetime NULL, parent_activity_code VARCHAR(10) NULL ) GO ALTER TABLE project_activity_schedule ADD CONSTRAINT fk_parent_child_activity FOREIGN KEY (parent_activity_code) REFERENCES project_activity_schedule(activity_code) GO CREATE FUNCTION fnHierarchyLevel ( @activity_code VARCHAR(10) ) RETURNS INT BEGIN DECLARE @i INT SET @i = 0 WHILE EXISTS (SELECT parent_activity_code FROM project_activity_schedule WHERE activity_code = @activity_code) SELECT @i = @i + 1, @activity_code = parent_activity_code FROM project_activity_schedule WHERE activity_code = @activity_code RETURN @i END GO CREATE FUNCTION fnHierarchyOrder ( @activity_code VARCHAR(10) ) RETURNS VARCHAR(8000) BEGIN DECLARE @s VARCHAR(8000) DECLARE @t TABLE ( ColA INT NOT NULL IDENTITY, Value VARCHAR(10) ) SET @s = ’’ WHILE EXISTS (SELECT parent_activity_code FROM project_activity_schedule WHERE activity_code = @activity_code) BEGIN INSERT @t VALUES(@activity_code) SELECT @activity_code = parent_activity_code FROM project_activity_schedule WHERE activity_code = @activity_code END SELECT @s = @s + Value FROM @t ORDER BY ColA DESC RETURN @s END GO SET NOCOUNT ON INSERT project_activity_schedule VALUES(’A1’, ’Site clearance - clearing of trees, garbage, etc.’, ’20000620’, NULL) INSERT project_activity_schedule VALUES(’A2’, ’Excavation for foundation’, ’20000623’, NULL) INSERT project_activity_schedule VALUES(’A2.1’, ’Arrangement of 25 unskilled laborers’, ’20000623’, ’A2’) INSERT project_activity_schedule VALUES(’A2.2’, ’Arrange of machinery’, ’20000623’, ’A2’) INSERT project_activity_schedule VALUES(’A2.3’, ’Excavation up to 25m x 25m x 1m’, ’20000623’, ’A2’) INSERT project_activity_schedule VALUES(’A3’, ’Laying of foundation’, ’20000630’, NULL) INSERT project_activity_schedule VALUES(’A3.1’, ’Foundation activity 1’, ’20000630’, ’A3’) INSERT project_activity_schedule VALUES(’A3.2’, ’Foundation activity 2’, ’20000707’, ’A3’) DECLARE @indent_string VARCHAR(25) SET @indent_string = CHAR(9) SELECT Tree = REPLICATE(@indent_string, dbo.fnHierarchyLevel(activity_code) - 1) + activity_code + ’ ’ + activity_description + ’ (’ + CONVERT(VARCHAR, planned_start_date) + ’)’ FROM project_activity_schedule ORDER BY activity_code SELECT Tree = REPLICATE(@indent_string, dbo.fnHierarchyLevel(activity_code) - 1) + activity_code + ’ ’ + activity_description + ’ (’ + CONVERT(VARCHAR, planned_start_date) + ’)’ FROM project_activity_schedule ORDER BY dbo.fnHierarchyOrder(activity_code) SET NOCOUNT OFF GO DROP TABLE project_activity_schedule DROP FUNCTION dbo.fnHierarchyLevel DROP FUNCTION dbo.fnHierarchyOrder GO Excuse me for my poor english. Sincerely, Alejandro Mesa]]>Alejandro Mesa Mon, 16 Dec 2002 07:42:34 GMThttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorFri, 13 Dec 2002 12:00:53 GMT
Another way to generate resultset in tree form, is adding a column to the table (or create a view or use the function in the select statement) to record the level in the tree, then you can use this value to generate the indent. Example: (I use same table definition and sample data) CREATE FUNCTION fnMyFunction ( @activity_code VARCHAR(10) ) RETURNS INT BEGIN DECLARE @i INT SET @i = 0 IF @activity_code IS NOT NULL WHILE @activity_code IS NOT NULL BEGIN SET @i = @i + 1 SELECT @activity_code = parent_activity_code FROM project_activity_schedule WHERE activity_code = @activity_code END RETURN @i END GO CREATE TABLE project_activity_schedule ( activity_code VARCHAR(10) NOT NULL PRIMARY KEY, activity_description VARCHAR(255) NOT NULL, planned_start_date datetime NULL, parent_activity_code VARCHAR(10) NULL, treeLevel AS dbo.fnMyFunction(parent_activity_code) ) GO ALTER TABLE project_activity_schedule ADD CONSTRAINT fk_parent_child_activity FOREIGN KEY (parent_activity_code) REFERENCES project_activity_schedule(activity_code) GO SET NOCOUNT ON INSERT project_activity_schedule VALUES(’A1’, ’Site clearance - clearing of trees, garbage, etc.’, ’20000620’, NULL) INSERT project_activity_schedule VALUES(’A2’, ’Excavation for foundation’, ’20000623’, NULL) INSERT project_activity_schedule VALUES(’A2.1’, ’Arrangement of 25 unskilled laborers’, ’20000623’, ’A2’) INSERT project_activity_schedule VALUES(’A2.2’, ’Arrange of machinery’, ’20000623’, ’A2’) INSERT project_activity_schedule VALUES(’A2.3’, ’Excavation up to 25m x 25m x 1m’, ’20000623’, ’A2’) INSERT project_activity_schedule VALUES(’A3’, ’Laying of foundation’, ’20000630’, NULL) INSERT project_activity_schedule VALUES(’A3.1’, ’Foundation activity 1’, ’20000630’, ’A3’) INSERT project_activity_schedule VALUES(’A3.2’, ’Foundation activity 2’, ’20000707’, ’A3’) DECLARE @indent_string VARCHAR(25) SET @indent_string = CHAR(9) SELECT REPLICATE(@indent_string, treeLevel) + activity_code + ’ ’ + activity_description + ’ (’ + CONVERT(VARCHAR, planned_start_date) + ’)’ FROM project_activity_schedule SET NOCOUNT OFF GO DROP TABLE project_activity_schedule DROP FUNCTION fnMyFunction GO Result: -------------------------------------------------------------------------- A1 Site clearance - clearing of trees, garbage, etc. (Jun 20 2000 12:00AM) A2 Excavation for foundation (Jun 23 2000 12:00AM) A2.1 Arrangement of 25 unskilled laborers (Jun 23 2000 12:00AM) A2.2 Arrange of machinery (Jun 23 2000 12:00AM) A2.3 Excavation up to 25m x 25m x 1m (Jun 23 2000 12:00AM) A3 Laying of foundation (Jun 30 2000 12:00AM) A3.1 Foundation activity 1 (Jun 30 2000 12:00AM) A3.2 Foundation activity 2 (Jul 7 2000 12:00AM)]]>Alejandro Mesa Fri, 13 Dec 2002 12:00:53 GMThttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorhttp://www.windowsitpro.com/article/internet/keep-database-schema-online#commentsAnchorWed, 26 Dec 2001 15:40:52 GMT
Thanks so much for your article 15456 in SQLServer Mag Nov 2000 issue. I am new to ASP, but after a few tries, I was able to create an ASP from your scripts. I found a couple of things confusing. 1. The article referred to Fig. 1,page 56 - SQL Server diagram without column properties - I thought this diagram has column properties and it’s actually the result that I got from running the downloaded code. 2. I was given the impression that instead of the result that I got (and quite happy with actually), that I would produce actual DB diagrams like the articul captioned on page s 54 and 55. Please clarify. Sincerely, Taiwo]]>Taiwo Ogunyanlkin Wed, 26 Dec 2001 15:40:52 GMThttp://www.windowsitpro.com/article/internet/keep-database-schema-online#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorFri, 27 Jul 2001 11:06:19 GMT
This solution works but it can be better. Recursive stored procedures are still limited to 32 levels deep. A better alternative is to create a temporary table and fill that table the top level parents. Then repeatedly fill the table with children of the parent already in the table until no records are added. Sorting is accomplished by building a sort string as you go (sort_order = SPACE(tree_level) + parent_key + child_key). This needs one procedure one local temporary table and one loop.]]>BillFri, 27 Jul 2001 11:06:19 GMThttp://www.windowsitpro.com/article/tsql3/using-t-sql-to-generate-a-resultset-in-tree-form#commentsAnchorhttp://www.windowsitpro.com/article/internet/keep-database-schema-online#commentsAnchorWed, 13 Jun 2001 04:55:45 GMT
"Keep Database Schema Online Build an ASP page that lets you access schema anytime" In this article Listing 1 is empty. Perhaps ...]]>Robin Moest Wed, 13 Jun 2001 04:55:45 GMThttp://www.windowsitpro.com/article/internet/keep-database-schema-online#commentsAnchor