Advertisement
Advertisement
| 08.03.2008 at 05:08PM PDT, ID: 23617757 |
|
[x]
Attachment Details
|
||
|
[x]
The Solution Rating System
|
||
With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.
Your Input Matters If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support. Thank you! |
||
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: |
-- CREATE ADJACENT-LIST/PARENT-CHILD TREE
CREATE TABLE dbo.sparse_tree_pc
(
node_id int NOT NULL IDENTITY(1,1),
parent_id int NULL,
node_name varchar(255) NOT NULL,
sparse_int int NULL,
sparse_date datetime NULL,
CONSTRAINT pk_sparse_tree_pc PRIMARY KEY (node_id),
CONSTRAINT fk_sparse_tree_pc_pid FOREIGN KEY (parent_id) REFERENCES sparse_tree_pc
)
GO
-- INSERT STANDING DATA
INSERT INTO dbo.sparse_tree_pc VALUES (NULL, 'Colors', 10, GETDATE())
INSERT INTO dbo.sparse_tree_pc VALUES (1, 'Red', 5, NULL)
INSERT INTO dbo.sparse_tree_pc VALUES (1, 'Yellow', NULL, '01-Jan-2008')
INSERT INTO dbo.sparse_tree_pc VALUES (1, 'Green', NULL, NULL)
INSERT INTO dbo.sparse_tree_pc VALUES (2, 'Cherry', NULL, NULL)
INSERT INTO dbo.sparse_tree_pc VALUES (2, 'Postbox', 7, '02-Feb-2008')
INSERT INTO dbo.sparse_tree_pc VALUES (3, 'Fruit', NULL, NULL)
INSERT INTO dbo.sparse_tree_pc VALUES (7, 'Lemon', 12, '03-Mar-2008')
INSERT INTO dbo.sparse_tree_pc VALUES (7, 'Banana', 17, NULL)
INSERT INTO dbo.sparse_tree_pc VALUES (3, 'Tennis ball', 6, NULL)
INSERT INTO dbo.sparse_tree_pc VALUES (4, 'Grass', NULL, '04-Apr-2008')
INSERT INTO dbo.sparse_tree_pc VALUES (4, 'Leaf', NULL, NULL)
GO
-- PATH PADDER
DECLARE @pad INT, @stuff VARCHAR(MAX);
SELECT @pad = LEN(CAST(MAX(node_id) AS VARCHAR)) FROM sparse_tree_pc;
SELECT @stuff = REPLICATE('0', @pad);
-- STANDARD TREE QUERY
WITH rec(node_id, parent_id, node_name, sparse_int, sparse_date, level, path) AS (
SELECT node_id,
parent_id,
node_name,
sparse_int,
sparse_date,
0,
CAST('/' + RIGHT(@stuff + CAST(node_id AS VARCHAR), @pad) AS VARCHAR)
FROM sparse_tree_pc
WHERE parent_id IS NULL
UNION ALL
SELECT t.node_id,
t.parent_id,
t.node_name,
t.sparse_int,
t.sparse_date,
r.level + 1,
CAST(r.path + '/' + RIGHT(@stuff + CAST(t.node_id AS VARCHAR), @pad) AS VARCHAR)
FROM rec r
INNER JOIN sparse_tree_pc t ON t.parent_id = r.node_id
)
SELECT node_id,
parent_id,
REPLICATE('...', level) + node_name AS node_name,
sparse_int,
sparse_date,
level,
path
FROM rec
ORDER BY path;
-- INHERIT-FROM-PARENT TREE QUERY
WITH rec(node_id, parent_id, node_name, sparse_int, sparse_date, level, path) AS (
SELECT node_id,
parent_id,
node_name,
sparse_int,
sparse_date,
0,
CAST('/' + RIGHT(@stuff + CAST(node_id AS VARCHAR), @pad) AS VARCHAR)
FROM sparse_tree_pc
WHERE parent_id IS NULL
UNION ALL
SELECT t.node_id,
t.parent_id,
t.node_name,
COALESCE(t.sparse_int, r.sparse_int),
COALESCE(t.sparse_date, r.sparse_date),
r.level + 1,
CAST(r.path + '/' + RIGHT(@stuff + CAST(t.node_id AS VARCHAR), @pad) AS VARCHAR)
FROM rec r
INNER JOIN sparse_tree_pc t ON t.parent_id = r.node_id
)
SELECT node_id,
parent_id,
REPLICATE('...', level) + node_name AS node_name,
sparse_int,
sparse_date,
level,
path
FROM rec
ORDER BY path
|
