query
-
Dear all,
I have following table
id id1 id2 id3 id4 description
1 1 0 0 0 A
2 1 1 0 0 B
3 1 1 1 0 C
4 1 1 1 1 D
5 1 2 0 0 BB
6 1 2 1 0 CC
7 1 2 1 1 DD
id is an auto incremental column and rest are generated manually for different purposes.
id is the primary key.
its a tree structure..
if user searches for D, i want to display A-B-C-D
if user searches for DD, i want to display A-BB-CC-DD
pls help me to generate sql
thanks
peter
-
can someone here please help me???
-
There is probably a more elegant and efficient way of doing it (see what the gurus reply with) but this works (see attachment as it wont let me post the code). You can change the where clause to suit your needs or if you remove it all together, it will return all the folder paths.
-
thank you very much, but one small change if search C or any value between ,,its not returning anything.
for example if i search c i should get a-b-c
if i search cc i should a-bb-cc
your query is correct, but with lil changes it will be realyl super.
-
Using the sample data provided by burfos: -
DECLARE @values TABLE(
id INT,
id1 INT,
id2 INT,
id3 INT,
id4 INT,
[description] VARCHAR(10)
);
INSERT INTO @values
VALUES ( 1, 1, 0, 0, 0, 'A' );
INSERT INTO @values
VALUES ( 2, 1, 1, 0, 0, 'B' );
INSERT INTO @values
VALUES ( 3, 1, 1, 1, 0, 'C' );
INSERT INTO @values
VALUES ( 4, 1, 1, 1, 1, 'D' );
INSERT INTO @values
VALUES ( 5, 1, 2, 0, 0, 'BB' );
INSERT INTO @values
VALUES ( 6, 1, 2, 1, 0, 'CC' );
INSERT INTO @values
VALUES ( 7, 1, 2, 1, 1, 'DD' );
Assuming that "description" is unique, something like this would do it: -
DECLARE @SearchCriteria VARCHAR(10);SET @SearchCriteria = 'DD';
SELECT STUFF((SELECT '-' + [description]
FROM @values
WHERE id <= (
SELECT id
FROM @values AS [v]
WHERE [v].[description] = @SearchCriteria
)
ORDER BY id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
If description is not unique, then you'll run in to an error due to the subquery needing to return a single value.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
peterausger (1/9/2015)
thank you very much, but one small change if search C or any value between ,,its not returning anything.for example if i search c i should get a-b-c
if i search cc i should a-bb-cc
your query is correct, but with lil changes it will be realyl super.
ok so change the where to be something like
where ('-' + f1.description + ISNULL('-' + f2.description, '') + ISNULL('-' + f3.description, '') + ISNULL('-' + f4.description, '')) LIKE ('%-' + @SearchCriteria )
-
I suspect recursion would be great for this, but that's not my specialty. This is somewhat akin to the earlier LOJ version, which unfortunately I didn't see until now or I maybe could have saved my time doing it :crazy:
CREATE TABLE #values
(
id INT,
id1 INT,
id2 int,
id3 int,
id4 int,
[description] varchar(30)
);
INSERT INTO #values
VALUES ( 1, 1, 0, 0, 0, 'A' );
INSERT INTO #values
VALUES ( 2, 1, 1, 0, 0, 'B' );
INSERT INTO #values
VALUES ( 3, 1, 1, 1, 0, 'C' );
INSERT INTO #values
VALUES ( 4, 1, 1, 1, 1, 'D' );
INSERT INTO #values
VALUES ( 5, 1, 2, 0, 0, 'BB' );
INSERT INTO #values
VALUES ( 6, 1, 2, 1, 0, 'CC' );
INSERT INTO #values
VALUES ( 7, 1, 2, 1, 1, 'DD' );
DECLARE @description varchar(30)
SET @description = 'DD'
SELECT
REVERSE(v_lowest_level.description +
ISNULL('-' + v_up_1_level.description, '') +
ISNULL('-' + v_up_2_levels.description, '') +
ISNULL('-' + v_up_3_levels.description, '')) AS combined_descriptions
FROM #values v_lowest_level
CROSS APPLY (
SELECT CASE WHEN id4 > 0 THEN 1 ELSE 0 END + CASE WHEN id3 > 0 THEN 1 ELSE 0 END +
CASE WHEN id2 > 0 THEN 1 ELSE 0 END + 1 AS id_count
) AS assign_alias_names
LEFT OUTER JOIN #values v_up_1_level ON
id_count > 1 AND
v_up_1_level.id1 = v_lowest_level.id1 AND
(v_up_1_level.id2 = CASE WHEN id_count > 2 THEN v_lowest_level.id2 ELSE 0 END) AND
(v_up_1_level.id3 = CASE WHEN id_count > 3 THEN v_lowest_level.id3 ELSE 0 END) AND
v_up_1_level.id4 = 0
LEFT OUTER JOIN #values v_up_2_levels ON
id_count > 2 AND
v_up_2_levels.id1 = v_lowest_level.id1 AND
(v_up_2_levels.id2 = CASE WHEN id_count > 3 THEN v_lowest_level.id2 ELSE 0 END) AND
v_up_2_levels.id3 = 0 AND
v_up_2_levels.id4 = 0
LEFT OUTER JOIN #values v_up_3_levels ON
id_count > 3 AND
v_up_3_levels.id1 = v_lowest_level.id1 AND
v_up_3_levels.id2 = 0 AND
v_up_3_levels.id3 = 0 AND
v_up_3_levels.id4 = 0
WHERE v_lowest_level.description = @description
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
peterausger (1/8/2015)
Dear all,I have following table
id id1 id2 id3 id4 description
1 1 0 0 0 A
2 1 1 0 0 B
3 1 1 1 0 C
4 1 1 1 1 D
5 1 2 0 0 BB
6 1 2 1 0 CC
7 1 2 1 1 DD
id is an auto incremental column and rest are generated manually for different purposes.
id is the primary key.
its a tree structure..
if user searches for D, i want to display A-B-C-D
if user searches for DD, i want to display A-BB-CC-DD
pls help me to generate sql
thanks
peter
Which tree structure of the following would you say represents your data (top or bottom)? If neither, please let us know what the tree structure actually looks like because I have some ideas that could help you out a lot for the future.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Bit late to the party but you can convert this to a nested set representation using the code below
See Jeff Moden articles here[/url] for info on nested sets
WITH Source AS (
SELECT id,id1,id2,id3,id4,description,
SIGN(id1)+SIGN(id2)+SIGN(id3)+SIGN(id4) AS NodeLevel,
ROW_NUMBER() OVER(ORDER BY id1,id2,id3,id4) AS NodesVisited,
CASE WHEN id4 <> 0 THEN COUNT(*) OVER(PARTITION BY id2,id2,id3,id4)
WHEN id3 <> 0 THEN COUNT(*) OVER(PARTITION BY id1,id2,id3)
WHEN id2 <> 0 THEN COUNT(*) OVER(PARTITION BY id1,id2)
ELSE COUNT(*) OVER(PARTITION BY id1)
END - 1 AS NumberSubnodes
FROM #values)
SELECT *,
NodesVisited*2 - NodeLevel AS lft,
NodesVisited*2 - NodeLevel + NumberSubnodes*2 + 1 AS rgt
FROM Source;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark Cowne (1/11/2015)
Bit late to the party but you can convert this to a nested set representation using the code belowKind of. Both Hierarchical Path and Nested Sets are a bitch to maintain. It would be far better to convert this to an Adjacency List and then use the information in that article to convert it to Nested Sets whenever there's a change.
BTW... nice code and very interesting the way you did that, Mark.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply