February 6, 2011 at 10:28 am
hi there,
i'm quite new to complex t-sql querys and want to solve following problem:
this is a given table structure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SEGMENTE](
[SEGMENTE_KEY] [int] NOT NULL, /* primary key */
[SEGMENTE_MASTERKEY] [int] NOT NULL, /* this might be the reference of the SEGMENTE_KEY */
[SEGMENTE_NAME] [varchar](100) NOT NULL, /* name of the node */
[SEGMENTE_SORT] [int] NOT NULL, /* here it is possible to manually sort the nodes... */
CONSTRAINT [PK_SEGMENT] PRIMARY KEY CLUSTERED
(
[SEGMENTE_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SEGMENTE] ADD CONSTRAINT [DF_SEGMENT_SEGMENT_ORDER] DEFAULT ((0)) FOR [SEGMENTE_SORT]
GO
ALTER TABLE [dbo].[SEGMENTE] ADD CONSTRAINT [DF_SEGMENTE_SEGMENTE_NOTACTIVE] DEFAULT ((0)) FOR [SEGMENTE_NOTACTIVE]
GO
that means i have some kind of hierachical/tree structure.
inserted data could be like this (please whatch the screenshot):
SEGMENTE_KEYSEGMENTE_MASTERKEYSEGMENTE_NAMESEGMENTE_SORTHierarchyLevelPfadSortMyOrder
10000Planung/Ladenbau101Planung/Ladenbau10110100000000
10071000Planungsbüros12Planung/Ladenbau>Planungsbüros1011210112000000
10081000Ladenbauer22Planung/Ladenbau>Ladenbauer1012210122000000
10091000Architekten32Planung/Ladenbau>Architekten1013210132000000
1000test11test1111000000000
101100sub eins12test>sub eins111211120000000
102101sub eins eins13test>sub eins>sub eins eins11121311121300000
103101sub eins zwei23test>sub eins>sub eins zwei11122311122300000
104100sub zwei22test>sub zwei112211220000000
105104sub zwei eins13test>sub zwei>sub zwei eins11221311221300000
106104sub zwei zwei23test>sub zwei>sub zwei zwei11222311222300000
10010Ladeneinrichtung201Ladeneinrichtung20120100000000
10101001Sonstiges12Ladeneinrichtung>Sonstiges2011220112000000
10111010Wandabwicklung13Ladeneinrichtung>Sonstiges>Wandabwicklung201121320112130000
11001011Gitterregale14Ladeneinrichtung>Sonstiges>Wandabwicklung>Gitterregale20112131420112131400
11011011Wandregale24Ladeneinrichtung>Sonstiges>Wandabwicklung>Wandregale20112132420112132400
11031011Rückwände mit Fachböden34Ladeneinrichtung>Sonstiges>Wandabwicklung>Rückwände mit Fachböden20112133420112133400
this means there should be a possibility to manually determine a kind of custom sort order. so the SEGMENTE_SORT-column determines how to order the different nodes of the tree.
so i tried to solve the problem like this:
WITH tree (SEGMENTE_KEY,SEGMENTE_MASTERKEY,SEGMENTE_NAME, SEGMENTE_SORT,HierarchyLevel,Pfad,Sort) AS
(
SELECT SEGMENTE_KEY,SEGMENTE_MASTERKEY,SEGMENTE_NAME,SEGMENTE_SORT, 1 as HierarchyLevel, convert(nvarchar(512),COALESCE(SEGMENTE_NAME,'')) as Pfad, convert(nvarchar(128),COALESCE((CONVERT(nvarchar(4),SEGMENTE_SORT)+'1'),'')) as Sort
FROM SEGMENTE
WHERE SEGMENTE_MASTERKEY = 0
UNION ALL
SELECT e.SEGMENTE_KEY, e.SEGMENTE_MASTERKEY, e.SEGMENTE_NAME, e.SEGMENTE_SORT, eh.HierarchyLevel + 1 AS HierarchyLevel, CONVERT(nvarchar(512),COALESCE((eh.Pfad + '>' + e.SEGMENTE_NAME),'')) AS Pfad, convert(nvarchar(128),COALESCE(eh.Sort+(CONVERT(nvarchar(4),e.SEGMENTE_SORT))+convert(nvarchar(4),(eh.HierarchyLevel + 1)),'')) as Sort
FROM SEGMENTE e
INNER JOIN tree eh ON e.SEGMENTE_MASTERKEY = eh.SEGMENTE_KEY
)
SELECT tree.SEGMENTE_KEY, tree.SEGMENTE_MASTERKEY, tree.SEGMENTE_NAME, tree.SEGMENTE_SORT, tree.HierarchyLevel, tree.Pfad, tree.Sort
,COALESCE(tree.Sort+REPLICATE('0', ((select MAX(Len(tree.Sort)) from tree)-LEN(tree.Sort))),tree.Sort) as MyOrder
FROM tree
ORDER BY
convert(float,COALESCE(tree.Sort+REPLICATE('0', ((select MAX(Len(tree.Sort)) from tree)-LEN(tree.Sort))),tree.Sort)) asc;
that means, i am able to retrieve the hierarchical level of every branch. together with the sort-order i try to make a certain kind of order-key. finally i sort the resultset by this order-key.
the only problem i have now is a sortorder of 10, 20, 100 aso is always ordered on the top of the list.
how can i get arround this problem (create a better order-key) in order to sort records with lower SEGMENTE_SORT values topmost, at the same time not loose the hierachical connection?
any suggestions?
thanks a lot!
February 6, 2011 at 12:26 pm
What exactly are you trying to do?
Based on your sample data the segment_key=100 has an assigned value of sort=11.
Why should this sort value be listed before a sort=101?
Based on your data I'd consider the shown result as correct. It would be a difference if the value would be 10 instead of 11 though...
February 6, 2011 at 1:07 pm
sorry for my bad explenation!
actually if you are looking on the screenshot the segmente_sort - column is the sort-order supposed to be.
that means the sort-order i'm trying to achive should be like this (refering to the segmente_key):
the 'test'-branch
100, 101, 102, 103, 104, 105, 106
the 'planung/ladenbau'-branch
1000, 1007, 1008, 1009
aso...
the solumns 'Sort', 'MyOrder' is just a try to create a sortable order-key - but as i tried to explain it doesn't work like supposes to...
regards
February 6, 2011 at 4:01 pm
In your CTE, add another column that will hold the value of the SEGMENT_SORT column (modified to include leading Zero's) for SEGMENTE_MASTERKEY = 0 values. Place this value column at the beginning of your MyOrder formula.
So your data would look like:
the 'test'-branch
100, 00111000000000
101, 00111120000000
102, 00111121300000
103, 00111122300000
104, 00111220000000
105, 00111221300000
106, 00111222300000
and the 'planung/ladenbau'-branch
1000, 01010100000000
1007, 01010112000000
1008, 01010122000000
1009, 01010132000000
where the first three character will hold the SEGMENT_SORT values with leading Zero's.
For a coded version please provide ready to use sample data as described in the first article referenced in my signature.
February 7, 2011 at 1:25 am
thank you lutz!
as far as i understand your suggestion it won't solve the problem.
in my cte code i finally order convert the former constructed 'myorder'-key into a number type and then i'm able to sort the resultset in a number manner. that means, leading zeros won't solve the problem if i'm sorting the key as string (type) neither, am i wright?
also the segmente_masterkey could be a very low number like (10, 20, 30) but has to be sortedt at the end of the resultset because the segmente_sort might overrule this by having a quite high numeric value (like, 100, 200 in comparison to the other branches)...
so the main thing is how to creat a order-key that can be sorted (numerical or alphanumerical) by the segmente_sort and the hierarchy, so to speak some kind of advanced sub-grouping/sub-sorting for every branch of the tree?
thank you!
February 7, 2011 at 1:26 pm
It took me a while to convert the sample data into a ready to use format...
Based on what I've seen all you'd need to do is change your original query slightly.
I added a dynamic value based on the max. length of SEGMENTE_SORT:
DECLARE @max_segSort TINYINT
SELECT @max_segSort =MAX(LEN(segmente_sort)) FROM SEGMENTE;
WITH tree (SEGMENTE_KEY,SEGMENTE_MASTERKEY,SEGMENTE_NAME, SEGMENTE_SORT,HierarchyLevel,Pfad,Sort) AS
(
SELECT
SEGMENTE_KEY,
SEGMENTE_MASTERKEY,
SEGMENTE_NAME,
SEGMENTE_SORT,
1 AS HierarchyLevel,
CONVERT(NVARCHAR(512),COALESCE(SEGMENTE_NAME,'')) AS Pfad,
CONVERT(NVARCHAR(128),COALESCE((CONVERT(NVARCHAR(4),POWER(10,@max_segSort)+SEGMENTE_SORT)+'1'),'')) AS Sort
FROM SEGMENTE
WHERE SEGMENTE_MASTERKEY = 0
UNION ALL
SELECT
e.SEGMENTE_KEY,
e.SEGMENTE_MASTERKEY,
e.SEGMENTE_NAME,
e.SEGMENTE_SORT,
eh.HierarchyLevel + 1 AS HierarchyLevel,
CONVERT(NVARCHAR(512),COALESCE((eh.Pfad + '>' + e.SEGMENTE_NAME),'')) AS Pfad,
CONVERT(NVARCHAR(128),COALESCE(eh.Sort+(CONVERT(NVARCHAR(4),e.SEGMENTE_SORT))+CONVERT(NVARCHAR(4),(eh.HierarchyLevel + 1)),'')) AS Sort
FROM SEGMENTE e
INNER JOIN tree eh ON e.SEGMENTE_MASTERKEY = eh.SEGMENTE_KEY
)
SELECT
tree.SEGMENTE_KEY,
tree.SEGMENTE_MASTERKEY,
tree.SEGMENTE_NAME,
tree.SEGMENTE_SORT,
tree.HierarchyLevel,
tree.Pfad,
tree.Sort
FROM tree
ORDER BY tree.sort
February 7, 2011 at 1:41 pm
Since you want to sort by hierarchy, then by the sort order column within the hierarchy, the easiest way to do this is to include the top level ID for the hierarchy in each level.
Add TopID to the CTE. In the base query (above the union statement), it's the ID of the row. In the recursive portion (below the union), it's the TopID from the CTE.
Order by that, then by the sort order column.
Avoid the zero-padding thing in the sort order. That converts the integer into a string, and will sort "1, 10, 100, 1000, 1001, 2, 20, 21, 3,...". Sorting by the TopID should cancel out the need for that.
WITH tree(SEGMENTE_KEY, SEGMENTE_MASTERKEY, SEGMENTE_NAME, SEGMENTE_SORT, HierarchyLevel, Pfad, Sort, TopID)
AS (SELECT SEGMENTE_KEY,
SEGMENTE_MASTERKEY,
SEGMENTE_NAME,
SEGMENTE_SORT,
1 AS HierarchyLevel,
CONVERT(NVARCHAR(512), COALESCE(SEGMENTE_NAME, '')) AS Pfad,
SEGMENTE_SORT,
SEGMENTE_KEY
FROM SEGMENTE
WHERE SEGMENTE_MASTERKEY = 0
UNION ALL
SELECT e.SEGMENTE_KEY,
e.SEGMENTE_MASTERKEY,
e.SEGMENTE_NAME,
e.SEGMENTE_SORT,
eh.HierarchyLevel + 1 AS HierarchyLevel,
CONVERT(NVARCHAR(512), COALESCE((eh.Pfad + '>'
+ e.SEGMENTE_NAME),
'')) AS Pfad,
e.SEGMENTE_SORT,
TopID
FROM SEGMENTE e
INNER JOIN tree eh
ON e.SEGMENTE_MASTERKEY = eh.SEGMENTE_KEY)
SELECT tree.SEGMENTE_KEY,
tree.SEGMENTE_MASTERKEY,
tree.SEGMENTE_NAME,
tree.SEGMENTE_SORT,
tree.HierarchyLevel,
tree.Pfad,
tree.Sort
FROM tree
ORDER BY TopID,
COALESCE(Sort, tree.HierarchyLevel) ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 1:49 pm
hi SSChampion,
sorry for that i've cause inconvenience!
wow, i didn't know that you will solve the whole problem...
thank you very much for the code, i have to analyze and understand it, nevertheless it seems to work!!!:w00t:
February 8, 2011 at 1:22 am
dear celko,
your commend is much to advanced for my knowledge (i am a sql rookie). you're speaking about optimisation? can you give a short example of code which demonstartes what you're exactly refering to?
Thanks!
February 8, 2011 at 7:19 am
Here's an article on hierarchies. It includes a link to Joe's data on the subject (which is must-read for any DBA).
http://www.sqlservercentral.com/articles/T-SQL/65540/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply