Tree Sort
-
I have a query that return output (tree) as
Level
Type
Product Description
a
A
Desc zzz
a;b
B
Desc avc
a;b;a1
C
Desc aas
a;b;a2
A
Desc a12
a;b;a2;a21
B
Desc vvq
a;b;a2;a21;a211
D
Desc abc
a;b;a2;a21;a212
A
Desc abd
a;b;a2;a21;a213
B
Desc ade
a;b;a2;a21;a214
D
Desc aab
a;b;a3
B
Desc acd
a;b;a4
C
Desc aaa
a;b;a5
B
Desc aca
a;b;a5;a51
C
Desc aacas
a;b;a5;a52
A
Desc bvd
a;b;a5;a53
A
Desc avd
The tree structure needs sorting based on tree level + type of material + description
The result should look like
a
A
Desc zzz
a;b
B
Desc avc
a;b;a2
A
Desc a12
a;b;a2;a21
B
Desc vvq
a;b;a2;a21;a212
A
Desc abd
a;b;a2;a21;a213
B
Desc ade
a;b;a2;a21;a214
D
Desc aab
a;b;a2;a21;a211
D
Desc abc (same Level, same type, then sort by prod. description)
a;b;a5
B
Desc aca
a;b;a5;a53
A
Desc avd
a;b;a5;a52
C
Desc bvd
a;b;a5;a51
C
Desc aacas
a;b;a3
B
Desc acd
a;b;a4
C
Desc aaa
a;b;a1
C
Desc aas
How do I achieve this sort ? Any help would be greatly appreciated. Thanks.
-
There are things still not clear for me.
You said: "The tree structure needs sorting based on tree level + type of material + description"
Then on the example why :
"a;b;a3" is after "a;b;a5"
* Noel -
Both are level 3 (3 items) and both have type of material as B.
Description 'Desc aca' comes before 'Desc acd' so 'a;b;a5' comes before 'a;b;a3'
-
Ah...
Try:
Select * from yourTable
ORDER BY Len(level)-len(replace(level,';','')), type, ProductDesc
Cheers,
[EDIT]: Wait a minute but if the level (for ordering purposes) is the number of items then, how come
"a;b;a5;a51" is before "a;b;a3"
* Noel -
First compare 'a;b;a5' and 'a;b;a3' Both have 3 items but the type of material is 'B' for the first one ('a;b;a5' ) and 'C' for the second ('a;b;a3' ). So 'a;b;a5' comes before 'a;b;a3'
It is then followed by Children of 'a;b;a5' before picking 'a;b;a3'
Hope this is clear.
-
Noeld, thanks for your input. But this still does not give me the result I expect.
-
Yes I know but I posted that I didn't understand the rules neither
You are trying to perform the ordering now on this fashion:
(level), (type),(sublevels),(description)
Which is not what you said on the begining
you need to be clear of you problem first. How to determine when a level is just a children and not a level?
I could perfectly well say that
"a;b;a1" is a children of "a;b"
and that will render the output example inconsistent
* Noel -
I have a suggestion which gives you what you want in your test case. It involves creating a user-defined function called sortFunction, which again uses a Numbers table. Create the function and the table once and for all. If the function works, but performance is bad, you should probably consider making the result of sortFunction a calculated column on your table and then put an index on it. I think that is possible, although I have never tried it...
-- Create a Numbers table
SELECT TOP 8000 n = IDENTITY(INT, 1, 1) INTO Numbers
FROM
sysobjects a1
CROSS JOIN
sysobjects a2
CROSS JOIN
sysobjects a3
ALTER TABLE Numbers
ADD CONSTRAINT Index_Numbers PRIMARY KEY CLUSTERED(n)
GO
-- Create test data
create table testtable (Level varchar(100) primary key, Type char(1), Description varchar(100))
go
insert testtable select 'a', 'A', 'Desc zzz'
insert testtable select 'a;b', 'B', 'Desc avc'
insert testtable select 'a;b;a1', 'C', 'Desc aas'
insert testtable select 'a;b;a2', 'A', 'Desc a12'
insert testtable select 'a;b;a2;a21', 'B', 'Desc vvq'
insert testtable select 'a;b;a2;a21;a211', 'D', 'Desc abc'
insert testtable select 'a;b;a2;a21;a212', 'A', 'Desc abd'
insert testtable select 'a;b;a2;a21;a213', 'B', 'Desc ade'
insert testtable select 'a;b;a2;a21;a214', 'D', 'Desc aab'
insert testtable select 'a;b;a3', 'B', 'Desc acd'
insert testtable select 'a;b;a4', 'C', 'Desc aaa'
insert testtable select 'a;b;a5', 'B', 'Desc aca'
insert testtable select 'a;b;a5;a51', 'C', 'Desc aacas'
insert testtable select 'a;b;a5;a52', 'A', 'Desc bvd'
insert testtable select 'a;b;a5;a53', 'A', 'Desc avd'
go
-- create function to sort data
create function sortFunction
(
@Level varchar(100)
)
returns varchar(8000)
as
begin
declare @Tree varchar(8000)
select @Tree = ''
SELECT @Tree = @Tree + Type + Description + replicate(' ', 100 - len(Description))
FROM Numbers cross join testtable
where
n <= LEN(@Level)
AND SUBSTRING(';' + @Level, n, 1) = ';'
AND Level = SUBSTRING(@Level, 1, CHARINDEX(';', @Level + ';', n) - 1)
order by n
return @Tree
end
go
-- Present sorted data
select * from testtable order by dbo.sortFunction(Level)
-- Clean up
drop function sortFunction
go
drop table testtable
go
drop table Numbers
go
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply