Order data after unpivot (with ugly order rules)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Order data after unpivot (with ugly order rules)

Author

Topic

LaurieCox

158 Posts

Posted - 2007-09-17 : 14:58:19

Hi,

This is a continuation of Uniquely defined Hierarchical Data: Part 3. Read the first post for a complete description of the problem.

Though I have gotten to this point on my own, I think I am hitting wall of my sql knowledge … I would really appreciate help. Ask me questions if any of this needs clarifying. I am regularly checking the threads. I do NOT want to go back to the beginning and write procedural code using cursors. The posts are long because it is a complicated problem but I am trying to be as concise as possible.

In part 3, I said I had divided the problem into the following steps.

Get all paths from the <category>_Link_Data tables that meet the given criteria.

Pull the <category>_Name and <category>_Text data into the path data

Break each path into rows with one row per level

Eliminate redundant paths

Get data to order correctly

Ready to write report in reporting services

… and had completed the code thru step 2.

I have now gotten thru steps 3 and 4. They turned out to be only one step due to the nature of the union statement removing duplicate rows. I did have to add one column to the ExpandedTree table to keep from eliminating rows that were not duplicates.

The ExpandedTree table now looks like this:


create table #ExpandedTree
 (CatagoryType char(1), -- A character representing the catagory:
                        -- (L)ibrary, (P)roblem, (G)oal, (O)bjective, (I)ntervention
  Parent       Int,     -- The Imediate parent of the node. This will keep the union
                        -- statement from deleting a branch that is in two paths at the
                        -- same level
  IndentLevel  Int,     -- When displayed in the tree view the indention level of the item
  ItemName     varchar(50), -- for reasons that do not matter called wiley_<category>_id 
                            -- in the <catagory>_data tables.
  ItemText     varchar(5000))

I took the data in the ValidPaths table and broke it out (unpivoted it?) into individual rows for each item on the given path.

(Note: all script along with sample data can be found in the second post of this thread)

For example this code will break out all of the Objective nodes found in the ValidPaths table:


select 'O', -- Objective
        COALESCE(nullif(GOALID,0),nullif(PROBID,0),LIBID), -- first non zero id on path
        case
            when PROBID = 0 and GOALID = 0 then 1 -- Library direct parent
            when PROBID > 0 and GOALID > 0 then 3 -- Goal is direct parent
            else 2                                -- Problem is direct parent
        end,
        Objective_Name,
        convert(varchar(5000),Objective_Text)
   from ##ValidPaths
  where OBJID > 0

The COALESCE statement determines who on the ValidPaths record is the Objectives immediate parent. I needed to this because in the following data in ValidPaths:


ID		FACILITY	LIBID	PROBID	GoalID	OBJID
1.1266.11.7.427	1		11	7	427	1266
1.1266.11.7.428	1		11	7	428	1266

… the same Goal (1266) is a direct child of two different Problems. If I did not include the Problem ID in the ExpandedTree table one of the rows would have been eliminated as a duplicate.

The case statement computes the level of the tree that the node must be displayed at. In this case an Objective can be at one of three levels.

It can be a direct child of a Library (the PROBID and GOALID fields will be zero).

It can be a direct child of a Problem (the PROBID is > 0 and the GOALID = 0)
Or it can be a direct child of a GOAL which in turn is a direct child of a Library
(PROBID = 0 and GOALID > 0)

It can be a child of a GOALID which is in turn a child of a PROBID (PROBID > 0 and GOALID > 0).

*I had opened a side bar thread in the Reporting Services forum to see if I was on the right track as far as getting this data in a format for reporting services. I got this one response:

quote:

This is a normal typical question for anyone new to RS. Your problem is very simple as this is exactly what RS can do. In fact the indentation that you have stated can be simulated using GROUP BY rows as well as a final details section. Each indentation is a group by. They follow one another I assume because you have foreign keys.

Due to the nature of the beast I don't think I do have any column that I can safely group by … and this leads me to my current problem: Ordering the data (step 5 above).

The code so far returns the data in this order:


CatagoryType	Parent	IndentLevel	ItemName		ItemText
G		7	2		Community		Community Participation Level
G		7	2		Social			Social Interaction Level
L		0	0		TestLinks3	
O		6	2		Access			Access to Community Resources
O		11	1		Activities		Participation in Community Activities
O		427	3		Access			Access to Community Resources
O		427	3		Activities		Participation in Community Activities
O		428	3		Activities		Participation in Community Activities
P		11	1		Productivity		Productivity
P		11	1		Supportive Needs	Supportive Needs

… and in order for it to display correctly it needs to be in this order:


CatagoryType	Parent	IndentLevel	ItemName		ItemText
L		0	0		TestLinks3	
P		11	1		Productivity		Productivity
O		6	2		Access			Access to Community Resources
P		11	1		Supportive Needs	Supportive Needs
G		7	2		Community		Community Participation Level
O		427	3		Access			Access to Community Resources
O		427	3		Activities		Participation in Community Activities
G		7	2		Social			Social Interaction Level
O		428	3		Activities		Participation in Community Activities
O		11	1		Activities		Participation in Community Activities

Notes:
Glossary:
Category: Library, Problem, Goal, Objective, Intervention
Level: How far to indent a branch of the tree so that it is under its direct partent.

When there are two (or more) nodes that are of the same category and on the same level with a common parent, they should be ordered alphabetically on ItemText.

Example the Goals; Community and Social both are children of the problem Supportive Needs. The Community Goal (and all its children) is listed first.

When two different categories are on the same level and are direct children of the same parents, the rows should be ordered by category. With the category order being Library, Problem, Goal, Objective and Intervention (as these do not order correctly alphabetically, I am thinking of putting a number in front of each category code: 1L, 2P, 3G, 4O, 5I).

Example: The problems Productivity and Supportive Needs are on the same level as the third instance of the Objective Activities. The problems come first (order by ItemText) and then the Objective is last

If I grouped on CatagoryType (after I got the data ordered) and ignored the IndentLevel the Objective Activities would be seen twice as a child of the Goal Social. Where it should be once as a child of the Goal and once as a Direct child of the Library.

The solution may lie in changing the IndentLevel column to some type of grouping value but I currently have no idea how to do this?

I think if I went back to step one and started writing some nested loop cursor garbage I could get the data formatted in the correct order. But I don't think it would be very pretty.

So my question is: What can be done using set based queries to get this data into the right order?

Also any comments/constructive criticism of the code so far would be greatly appreciated (especially the case and coalesce statements).

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2007-09-17 : 14:58:46

[code]
/****************** library *********************/

CREATE TABLE #Library_Data
(ID varchar(254),
FACILITY Int,
library_year varchar(50),
LIBID Int,
Library_Name varchar(50),
entry_date Datetime,
entry_time varchar(50),
entry_by varchar(50),
effective_date Datetime,
expiration_date Datetime,
library_origin varchar(50),
Wiley_Library_ID varchar(50),
active_code varchar(50),
active_value varchar(50))

/****************** Problems *********************/

CREATE TABLE #Problem_Data
(ID varchar(254),
FACILITY Int,
PROBID Int,
prob_Text ntext,
Wiley_Problem_ID varchar(50),
suggested_diagnosis ntext)

CREATE TABLE #Problem_Link_Data
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int)

/****************** Goals *********************/

CREATE TABLE #Goal_Data
(ID varchar(254),
FACILITY Int,
GoalID Int,
Goal_text ntext,
Wiley_Goal_ID varchar(50))

CREATE TABLE #Goal_Link_Data
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int,
GoalID Int)

/****************** Objectives *********************/

CREATE TABLE #Objective_Data
(ID varchar(254),
FACILITY Int,
OBJID Int,
objective_text ntext,
Wiley_Objective_ID varchar(50))

CREATE TABLE #Objective_Link_Data
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int,
GoalID Int,
OBJID Int)

/****************** Interventions *********************/

CREATE TABLE #Intervention_Data
(ID varchar(254),
FACILITY Int,
INTID Int,
intervention_text ntext,
Wiley_Intervention_ID varchar(50))

CREATE TABLE #Intervention_Link_Data
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int,
GoalID Int,
OBJID Int,
INTID Int)

/* Insert sample data */
INSERT INTO #library_data
SELECT '1||11',1,'1840',11,'TestLinks3', '2007-09-07 00:00:00.000',
'Laurie Cox', 'Laurie Cox', '2007-09-07 00:00:00.000', '',
'test', '', 'N', 'No'

INSERT INTO #Problem_Data
SELECT '1||6',1,6,'Productivity', 'Productivity', '' UNION ALL
SELECT '1||7',1,7,'Supportive Needs', 'Supportive Needs', '' UNION ALL
SELECT '1||90',1,90,'to test sorting stuff', 'A New Problem', ''

INSERT INTO #Problem_Link_Data
SELECT '1.6.11',1,11,6 UNION ALL
SELECT '1.7.11',1,11,7 UNION ALL
SELECT '1.90.11',1,11,90

INSERT INTO #goal_data
SELECT '1||427',1,427,'Community Participation Level', 'Community' UNION ALL
SELECT '1||428',1,428,'Social Interaction Level', 'Social' UNION ALL
SELECT '1||433',1,433,'Self Management', 'Self Management'

INSERT INTO #Goal_Link_Data
SELECT '1.427.11.7',1,11,7,427 UNION ALL
SELECT '1.428.11.7',1,11,7,428 UNION ALL
SELECT '1.433.11.90',1,11,90,433

INSERT INTO #Objective_Data
SELECT '1||1264',1,1264,'Participation in Social Groups', 'Groups' UNION ALL
SELECT '1||1265',1,1265,'Access to Community Resources', 'Access' UNION ALL
SELECT '1||1266',1,1266,'Participation in Community Activities', 'Activities'

INSERT INTO #Objective_Link_Data
SELECT '1.1266.11.0.0',1,11,0,0,1266 UNION ALL
SELECT '1.1265.11.6.0',1,11,6,0,1265 UNION ALL
SELECT '1.1265.11.7.427',1,11,7,427,1265 UNION ALL
SELECT '1.1266.11.7.427',1,11,7,427,1266 UNION ALL
SELECT '1.1264.11.7.428',1,11,7,428,1264 UNION ALL
SELECT '1.1266.11.7.428',1,11,7,428,1266 UNION ALL
SELECT '1.1264.11.90.433',1,11,90,433,1264

INSERT INTO #Intervention_Data
SELECT '1||2467',1,2467,'Provide Transportation', 'Transportation' UNION ALL
SELECT '1||2473',1,2473,'Community Intervention', 'Community Intervention'

INSERT INTO #Intervention_Link_Data
SELECT '1.2467.11.7.427.1265',1,11,7,427,1265,2467 UNION ALL
SELECT '1.2473.11.90.433.1264',1,11,90,433,1264,2473

/************************************************************************
Rearrange id field so that all paths are in category order:
Library, Goal, Objective, Intervention
This will be used to eliminate redundant paths
*************************************************************************/

update #Problem_Link_Data
set id = convert(varchar(10),LIBID)+ '.' + convert(varchar(10),PROBID)

update #Goal_Link_Data
set id = convert(varchar(10),LIBID)+ '.' + convert(varchar(10),PROBID) + '.' +
convert(varchar(10),GOALID)

update #Objective_Link_data
set id = convert(varchar(10),LIBID)+ '.' + convert(varchar(10),PROBID) + '.' +
convert(varchar(10),GOALID) + '.' + convert(varchar(10),OBJID)

update #Intervention_Link_data
set id = convert(varchar(10),LIBID)+ '.' + convert(varchar(10),PROBID) + '.' +
convert(varchar(10),GOALID) + '.' + convert(varchar(10),OBJID) + '.' +
convert(varchar(10),INTID)

/*************************************************************************/

/* Step one: Get all paths that meet the user's criteria */

/* Parameters:
LibraryList = csv string - turned into a temp table.
LevelList = csv string - doesn't need tobe turned into a temp table as
the charindex function will work on a csv list.
StringFind = wild carded string
*/
drop table #librarylist
create table #LibraryList
(
libid int
)
insert into #LibraryList
--select 10 union all
select 11

declare @StringFind varchar(100)
set @StringFind = '%community%'
--set @StringFind = '%'
declare @LevelList varchar(10)
set @LevelList = 'O,G,I'
--set @LevelList = 'P,I,O,G'
drop table ##ValidPaths
create table ##ValidPaths
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int,
GoalID Int,
OBJID Int,
INTID Int,
Library_Name Varchar(50),
Problem_Name Varchar(50),
Problem_text ntext,
Goal_Name varchar(50),
Goal_Text ntext,
Objective_Name varchar(50),
Objective_Text ntext,
Intervention_Name varchar(50),
Intervention_Text ntext)

/* Interventions */
if charindex('I',@LevelList) > 0
begin
insert into ##ValidPaths (ID,FACILITY,LIBID,PROBID,GoalID,OBJID,INTID)
select ID,
FACILITY,
LIBID,
PROBID,
GoalID,
OBJID,
INTID
from #Intervention_link_Data il
where exists
(select i.intid
from #Intervention_Data i
where i.Intervention_text like @StringFind
and i.intid = il.intid)
and il.libid in (select * from #LibraryList)
end

/*Objectives */
if charindex('O',@LevelList) > 0
begin
insert into ##ValidPaths (ID,FACILITY,LIBID,PROBID,GoalID,OBJID)
select ID,
FACILITY,
LIBID,
PROBID,
GoalID,
OBJID
from #Objective_link_Data ol
where exists
(select o.objid
from #Objective_Data o
where o.Objective_text like @StringFind
and o.objid = ol.objid)
and ol.libid in (select * from #LibraryList)
and not exists
(select oo.id
from #Objective_link_Data oo join ##ValidPaths v1 on v1.id like oo.id + '%'
where oo.id = ol.id)
end

/* Goals */
if charindex('G',@LevelList) > 0
begin
insert into ##ValidPaths (ID,FACILITY,LIBID,PROBID,GoalID)
select ID,
FACILITY,
LIBID,
PROBID,
GoalID
from #Goal_link_Data gl
where exists
(select g.GoalID
from #Goal_Data g
where g.Goal_text like @StringFind
and g.GoalID = gl.GoalID)
and gl.libid in (select * from #LibraryList)
and not exists
(select gg.id
from #Goal_link_Data gg join ##ValidPaths v1 on v1.id like gg.id + '%'
where gg.id = gl.id)
end

/* Problems */
if charindex('P',@LevelList) > 0
begin
insert into ##ValidPaths (ID,FACILITY,LIBID,PROBID)
select ID,
FACILITY,
LIBID,
PROBID
from #Problem_link_Data pl
where exists
(select p.PROBID
from #Problem_Data p
where p.Prob_text like @StringFind
and p.PROBID = pl.PROBID)
and pl.libid in (select * from #LibraryList)
and not exists
(select pp.id
from #Problem_link_Data pp join ##ValidPaths v1 on v1.id like pp.id + '%'
where pp.id = pl.id)
end

-- Add Library Data
Update ##ValidPaths
set ##ValidPaths.Library_Name = #Library_Data.library_name
from #Library_Data
where ##ValidPaths.libid = #Library_Data.libid

-- Add Problem Data
Update ##ValidPaths
set ##ValidPaths.Problem_Name = #Problem_Data.Wiley_Problem_ID,
##ValidPaths.Problem_Text = #Problem_Data.prob_Text
from #Problem_Data
where ##ValidPaths.PROBID = #Problem_Data.PROBID

-- Add Goal Data
Update ##ValidPaths
set ##ValidPaths.Goal_Name = #Goal_Data.Wiley_Goal_ID,
##ValidPaths.Goal_Text = #Goal_Data.Goal_Text
from #Goal_Data
where ##ValidPaths.GOALID = #Goal_Data.GOALID

-- Add Objective_Data
Update ##ValidPaths
set ##ValidPaths.Objective_Name = #Objective_Data.Wiley_Objective_ID,
##ValidPaths.Objective_Text = #Objective_Data.Objective_Text
from #Objective_Data
where ##ValidPaths.OBJID =#Objective_Data.OBJID

-- Add Intervention_Data
Update ##ValidPaths
set ##ValidPaths.Intervention_Name = #Intervention_Data.Wiley_Intervention_ID,
##ValidPaths.Intervention_Text = #Intervention_Data.Intervention_Text
from #Intervention_Data
where ##ValidPaths.INTID =#Intervention_Data.INTID

drop table #ExpandedTree
create table #ExpandedTree
(id varchar(50),
CatagoryType char(2), -- A character representing the catagory:
-- (L)ibrary, (P)roblem, (G)oal, (O)bjective, (I)ntervention
Parent Int, -- The Imediate parent of the node. This will keep the union
-- statement from deleting a branch that is in two paths at the
-- same level
IndentLevel Int, -- When displayed in the tree view the indention level of the item
ItemName varchar(50), -- for reasons that do not matter called wiley_<category>_id
-- in the <catagory>_data tables.
ItemText varchar(5000))

insert into #ExpandedTree
select id,'1L', -- Library
0, -- Libraries have no parents
0, -- Libraries will always be at the top of path
Library_Name,
null -- Libraries only have names (no text)
from ##ValidPaths
where LIBID > 0
union
select id,'2P', -- Problem
LIBID, -- The only parent a Problem can have is a Library.
1, -- Problems will always be second on a path
Problem_Name,
convert(varchar(5000),Problem_Text) -- union statement makes me change ntext to varchar
from ##ValidPaths
where PROBID > 0
union
select id,'3G', -- Goal
COALESCE(nullif(PROBID,0),LIBID), -- first non zero id on path
case
when PROBID = 0 then 1 -- Library is direct parent
else 2 -- Problem is direct parent
end,
Goal_Name,
convert(varchar(5000),Goal_Text)
from ##ValidPaths
where GOALID > 0
union
select id,'4O', -- Objective
COALESCE(nullif(GOALID,0),nullif(PROBID,0),LIBID), -- first non zero id on path
case
when PROBID = 0 and GOALID = 0 then 1 -- Library direct parent
when PROBID > 0 and GOALID > 0 then 3 -- Goal is direct parent
else 2 -- Problem is direct parent
end,
Objective_Name,
convert(varchar(5000),Objective_Text)
from ##ValidPaths
where OBJID > 0
union
select id,'5I',
COALESCE(nullif(OBJID,0),nullif(GOALID,0),nullif(PROBID,0),LIBID), -- first non zero id on path
case
when PROBID = 0 and GOALID = 0 and OBJID = 0 then 1 -- Library direct parent
when PROBID > 0 and GOALID > 0 and OBJID > 0 then 4 -- has three parents between it and lib
when PROBID = 0 and GOALID > 0 and OBJID > 0 or -- has two parents between it and lib
PROBID > 0 and GOALID = 0 and OBJID > 0 or
PROBID > 0 and GOALID > 0 and OBJID = 0 then 3
else 2 -- Has one parent between it and lib
end,
Intervention_Name,
convert(varchar(5000),Intervention_Text)
from ##ValidPaths
where INTID > 0

select * from #ExpandedTree
[/code]
Changed to reflect data in the following post. Plus some changes (described in the following) to the script.

LaurieCox

158 Posts

Posted - 2007-09-19 : 15:14:21

Hi,

I am still spinning my wheels on how to sort the data for the report. As I have gotten no responses yet I thought I would add a little more information. Figured maybe my problem is my posts are too short …

Note: I have added to the test data for my examples. I updated the second post with the new data and some changes in script.

I searched SQLTeam for: Hierarchy order and have found some things that might help me. I am currently looking at and trying to decipher the following:

Help with crosstab denormalize query?

Ordering within Hierarchy

TreeStructs

RetrieveTreeHierarchy

One of the problems that I could be having is the dynamic structure of the tree. There are five categories that a node can be (Library, Problem, Goal, Objective and Intervention), but a node can be the direct child of any "higher" category. A node can have multiple parents. The test data has both of these features.

The dynamic structure affects the way the thing should sort.

Anyway … after spot reading the above pages, I noticed that a couple of them talked about having a path for each node. The data as it comes to me does have a path built into the Id column of the <category>_Link_Data tables. But this path always put the id of the given <category> at the front of the list. I wrote some script to change this. So that the path is always written in the order: LIBID.PROGID.GOALID.OBJID.INTID.

I have modified the script to include the ID column in the ExpandedTree table. This of course obliterated the magic of the UNION statement and gave me more rows than I will want in the final report.

There were two types of duplicate rows that showed up

For any given branch of the tree a row will be in each <category>Link_Data table with a complete path down to the given category.


Example:
For the path: 
   Library: Testlink3
       Problem: Supportive Needs
           Goal: Community
                Objective: Access

The following rows will be in the link tables:


#problem_link_data
ID	FACILITY	LIBID	PROBID
11.7	1	11	7

#Goal_Link_data
ID	FACILITY	LIBID	PROBID	GoalID
11.7.427	1	11	7	427

#objective_Link_data
ID	FACILITY	LIBID	PROBID	GoalID	OBJID
11.7.427.1265	1	11	7	427	1265

Which (because both the Goal and the Objective meet the search criteria) generated the following rows in the #ValidPaths table:


11.7.427.1265 1  11   7   427 1265   TestLinks3  Supportive Needs  Community   Access 
11.7.427      1  11   7   427        TestLinks3  Supportive Needs  Community

I added the following code (for each category) to the script to eliminate these duplicates:


          and not exists
               (select pp.id
                  from #Problem_link_Data pp join ##ValidPaths v1 on v1.id like pp.id + '%'
                 where pp.id = pl.id)

Parents that share a common child will repeat for each child. I am hoping Reporting Services can help me get rid of these (see below).

So, the modifications to the test data and the script will give this result in ExpandedTree (I added the R# column so that you can see what rows were reordered in the expected results … see below):


R# id			CatagoryType	Parent	Indent	ItemName		ItemText			
01 11.0.0.1266		1L		0	0	TestLinks3			
02 11.0.0.1266		4O		11	1	Activities		Participation in Community Activities
03 11.6.0.1265		1L		0	0	TestLinks3			
04 11.6.0.1265		2P		11	1	Productivity		Productivity		
05 11.6.0.1265		4O		6	2	Access			Access to Community Resources
06 11.7.427.1265	1L		0	0	TestLinks3			
07 11.7.427.1265	2P		11	1	Supportive Needs	Supportive Needs		
08 11.7.427.1265	3G		7	2	Community		Community Participation Level	
09 11.7.427.1265	4O		427	3	Access			Access to Community Resources
10 11.7.427.1266	1L		0	0	TestLinks3			
11 11.7.427.1266	2P		11	1	Supportive Needs	Supportive Needs		
12 11.7.427.1266	3G		7	2	Community		Community Participation Level	
13 11.7.427.1266	4O		427	3	Activities		Participation in Community Activities
14 11.7.428.1266	1L		0	0	TestLinks3			
15 11.7.428.1266	2P		11	1	Supportive Needs	Supportive Needs		
16 11.7.428.1266	3G		7	2	Social			Social Interaction Level	
17 11.7.428.1266	4O		428	3	Activities		Participation in Community Activities
18 11.90.433.1264.2473	1L		0	0	TestLinks3			
19 11.90.433.1264.2473	2P		11	1	A New Problem		to test sorting stuff		
20 11.90.433.1264.2473	3G		90	2	Self Management		Self Management		
21 11.90.433.1264.2473	4O		433	3	Groups			Participation in Social Groups	
22 11.90.433.1264.2473	5I		1264	4	Community Intervention	Community Intervention

What I want to do with this data is sort it (keeping branches together) using the following rules:

When there are two (or more) nodes that are of the same category and on the same level with a common parent, they should be ordered alphabetically on ItemName (this is a change from the op (where it says ItemText).

Example the Goals; Community and Social both are children of the problem Supportive Needs. The Community Goal (and all its children) is listed first.

When two different categories are on the same level and are direct children of the same parents, the rows should be ordered by category. With the category order being Library, Problem, Goal, Objective and Intervention (as these do not order correctly alphabetically, I am thinking of putting a number in front of each category code: 1L, 2P, 3G, 4O, 5I).

Example: The problems Productivity and Supportive Needs are on the same level as the third instance of the Objective Activities. The problems come first (order by ItemText) and then the Objective is last

Magical sql goes here and I should end up with the following ordered dataset:


R# id			CatagoryType	Parent	Indent	ItemName		ItemText			
18 11.90.433.1264.2473	1L		0	0	TestLinks3			
19 11.90.433.1264.2473	2P		11	1	A New Problem		to test sorting stuff		
20 11.90.433.1264.2473	3G		90	2	Self Management		Self Management		
21 11.90.433.1264.2473	4O		433	3	Groups			Participation in Social Groups	
22 11.90.433.1264.2473	5I		1264	4	Community Intervention	Community Intervention	
03 11.6.0.1265		1L		0	0	TestLinks3			
04 11.6.0.1265		2P		11	1	Productivity		Productivity		
05 11.6.0.1265		4O		6	2	Access			Access to Community Resources
06 11.7.427.1265	1L		0	0	TestLinks3			
07 11.7.427.1265	2P		11	1	Supportive Needs	Supportive Needs		
08 11.7.427.1265	3G		7	2	Community		Community Participation Level	
09 11.7.427.1265	4O		427	3	Access			Access to Community Resources
10 11.7.427.1266	1L		0	0	TestLinks3			
11 11.7.427.1266	2P		11	1	Supportive Needs	Supportive Needs		
12 11.7.427.1266	3G		7	2	Community		Community Participation Level	
13 11.7.427.1266	4O		427	3	Activities		Participation in Community Activities
14 11.7.428.1266	1L		0	0	TestLinks3			
15 11.7.428.1266	2P		11	1	Supportive Needs	Supportive Needs		
16 11.7.428.1266	3G		7	2	Social			Social Interaction Level	
17 11.7.428.1266	4O		428	3	Activities		Participation in Community Activities
01 11.0.0.1266		1L		0	0	TestLinks3			
02 11.0.0.1266		4O		11	1	Activities		Participation in Community Activities

… and as I said above, I am hoping that I will be able to generate a report in Reporting Services that gets rid of duplicate parents (the crossed out rows above) and produces a report like this:

      
Library: TestLink3
    Problem: A New Problem                              P:90    to test sorting stuff
        Goal: Self Management                           G:433   Self Management
            Objective: Groups                           O:1264  Participation in Social Groups
                Intervention: Community Intervention    I:2473  Community Intervention
    Problem: Productivity                               P:6     Productivity
        Objective: Access                               O:1265  Access to Community Resources
    Problem: Supportive Needs                           P:7     Supportive Needs
        Goal: Community                                 G:427   Community Participation Level
            Objective: Access                           O:1265  Access to Community Resources
            Objective: Activities                       O:1266  Participation in Community Activities
        Goal: Social                                    G:428   Social Interaction Level
            Objective: Activities                       O:1266  Participation in Community Activities
    Objective: Activities                               O:1266  Participation in Community Activities

So can anybody help me with the magic?

Please?

Laurie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 15:19:36

Maybe if you posted some simpler sample data?
Cut things and post just the most important things.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 15:21:43

There are so much code. I have read it three times now and I understand what you are trying to do, but it all is too cluttered.
Scale it down, make it simpler.

Post just the basics.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 15:30:24

I am sorry you didn't like my two last comments.

E 12°55'05.25"
N 56°04'39.16"

LaurieCox

158 Posts

Posted - 2007-09-19 : 15:46:04

[code]
Hi Peso,

I know ... the thing is very complicated. My intent of the previous post (believe it or not) was to do just that (simplify). I got a little carried away.

I give the back ground steps in case some of the things I did at the beginning were wrong and the sort problem could have been (or needed to be) solved sooner.

Anyway currently I am here:
I have the table #ExpandedTree:
[code]
create table #ExpandedTree
(id varchar(50),
CatagoryType char(2), -- A character representing the catagory:
-- (L)ibrary, (P)roblem, (G)oal, (O)bjective, (I)ntervention
Parent Int, -- The Imediate parent of the node. This will keep the union
-- statement from deleting a branch that is in two paths at the
-- same level
IndentLevel Int, -- When displayed in the tree view the indention level of the item
ItemName varchar(50), -- for reasons that do not matter called wiley_<category>_id
-- in the <catagory>_data tables.
ItemText varchar(5000))
[/code]
Cut and paste the script (all of it) in the second post into query analyzer and run it. You will end up with #ExpandedTree having this data (except for the R# Column):
[code]
R# id CatagoryType Parent Indent ItemName ItemText
01 11.0.0.1266 1L 0 0 TestLinks3
02 11.0.0.1266 4O 11 1 Activities Participation in Community Activities
03 11.6.0.1265 1L 0 0 TestLinks3
04 11.6.0.1265 2P 11 1 Productivity Productivity
05 11.6.0.1265 4O 6 2 Access Access to Community Resources
06 11.7.427.1265 1L 0 0 TestLinks3
07 11.7.427.1265 2P 11 1 Supportive Needs Supportive Needs
08 11.7.427.1265 3G 7 2 Community Community Participation Level
09 11.7.427.1265 4O 427 3 Access Access to Community Resources
10 11.7.427.1266 1L 0 0 TestLinks3
11 11.7.427.1266 2P 11 1 Supportive Needs Supportive Needs
12 11.7.427.1266 3G 7 2 Community Community Participation Level
13 11.7.427.1266 4O 427 3 Activities Participation in Community Activities
14 11.7.428.1266 1L 0 0 TestLinks3
15 11.7.428.1266 2P 11 1 Supportive Needs Supportive Needs
16 11.7.428.1266 3G 7 2 Social Social Interaction Level
17 11.7.428.1266 4O 428 3 Activities Participation in Community Activities
18 11.90.433.1264.2473 1L 0 0 TestLinks3
19 11.90.433.1264.2473 2P 11 1 A New Problem to test sorting stuff
20 11.90.433.1264.2473 3G 90 2 Self Management Self Management
21 11.90.433.1264.2473 4O 433 3 Groups Participation in Social Groups
22 11.90.433.1264.2473 5I 1264 4 Community Intervention Community Intervention
[/code]
What I want to do with this data is sort it (keeping branches together) using the following rules:

When there are two (or more) nodes that are of the same category and on the same level with a common parent, they should be ordered alphabetically on ItemName (this is a change from the op (where it says ItemText).

Example the Goals; Community and Social both are children of the problem Supportive Needs. The Community Goal (and all its children) is listed first.

When two different categories are on the same level and are direct children of the same parents, the rows should be ordered by category. With the category order being Library, Problem, Goal, Objective and Intervention (as these do not order correctly alphabetically, I am thinking of putting a number in front of each category code: 1L, 2P, 3G, 4O, 5I).

Example: The problems Productivity and Supportive Needs are on the same level as the third instance of the Objective Activities. The problems come first (order by ItemName) and then the Objective is last

Magical sql goes here and I should end up with the following ordered dataset:
[code]
R# id CatagoryType Parent Indent ItemName ItemText
18 11.90.433.1264.2473 1L 0 0 TestLinks3
19 11.90.433.1264.2473 2P 11 1 A New Problem to test sorting stuff
20 11.90.433.1264.2473 3G 90 2 Self Management Self Management
21 11.90.433.1264.2473 4O 433 3 Groups Participation in Social Groups
22 11.90.433.1264.2473 5I 1264 4 Community Intervention Community Intervention
~~03 11.6.0.1265 1L 0 0 TestLinks3~~
04 11.6.0.1265 2P 11 1 Productivity Productivity
05 11.6.0.1265 4O 6 2 Access Access to Community Resources
~~06 11.7.427.1265 1L 0 0 TestLinks3~~
07 11.7.427.1265 2P 11 1 Supportive Needs Supportive Needs
08 11.7.427.1265 3G 7 2 Community Community Participation Level
09 11.7.427.1265 4O 427 3 Access Access to Community Resources
10 11.7.427.1266 1L 0 0 TestLinks3
11 11.7.427.1266 2P 11 1 Supportive Needs Supportive Needs
12 11.7.427.1266 3G 7 2 Community Community Participation Level
13 11.7.427.1266 4O 427 3 Activities Participation in Community Activities
14 11.7.428.1266 1L 0 0 TestLinks3
15 11.7.428.1266 2P 11 1 Supportive Needs Supportive Needs
16 11.7.428.1266 3G 7 2 Social Social Interaction Level
17 11.7.428.1266 4O 428 3 Activities Participation in Community Activities
~~01 11.0.0.1266 1L 0 0 TestLinks3~~
02 11.0.0.1266 4O 11 1 Activities Participation in Community Activities
[/code]

I am hoping Reporting Services can get rid of the crossed out lines. If not then the magical sql would also have to do that.

That’s about as basic as I can get.

Thanks,

Laurie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 16:02:34

What rule says group of "11.90.433.1264.2473" is sorted before the other?
Begin with explaing that.

Also I think you can make this simpler if you tell us in small steps what you want and provide proper and accurate sample data that is NOT CLUTTERED with irrelevant information.

E 12°55'05.25"
N 56°04'39.16"

LaurieCox

158 Posts

Posted - 2007-09-19 : 16:32:26

quote:
Originally posted by Peso

What rule says group of "11.90.433.1264.2473" is sorted before the other?
Begin with explaing that.

Because there are 6 rows of data that have an indent value of 1:


R# id			CatagoryType	Parent	Indent	ItemName		ItemText			
02 11.0.0.1266		4O		11	1	Activities		Participation in Community Activities
04 11.6.0.1265		2P		11	1	Productivity		Productivity		
07 11.7.427.1265	2P		11	1	Supportive Needs	Supportive Needs		
11 11.7.427.1266	2P		11	1	Supportive Needs	Supportive Needs		
15 11.7.428.1266	2P		11	1	Supportive Needs	Supportive Needs		
19 11.90.433.1264.2473	2P		11	1	A New Problem		to test sorting stuff

Productivity, Supportive Needs and A New Problem are all of the category "Problem".
Activities is of the category "Objective" from the second sort rule (in my third post) Problems come before Objectives (if they are on the same level).

The first rule stated that things of the same category and level should be alphabetized by ItemName and "A New Problem" comes first alphabetically within the Problem category.

The id numbers are assigned by the original application and have no meaning except they are unique within a given category.

quote:

Also I think you can make this simpler if you tell us in small steps what you want and provide proper and accurate sample data that is NOT CLUTTERED with irrelevant information.

I will try tomorrow to simplify the sample data. But if I simplify it too much than the resulting code may not work for some special case.

Thanks for your reply.

Laurie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 16:41:19

I still can't figure out why the "11.90..." group is sorted before the others...

E 12°55'05.25"
N 56°04'39.16"

LaurieCox

158 Posts

Posted - 2007-09-20 : 15:48:08

quote:
Originally posted by Peso

I still can't figure out why the "11.90..." group is sorted before the others...

Thank you !!! Thank you !!! Thank you !!! Thank You!!! Thank you !!! Thank You !!! Thank you !!! Thank You

I don't know if you were consciously applying the Socratic Method, but it worked out that way (but if I was you I would so claim it as so).

As I was trying to answer your question (see end of note), I realized that the path had to be sortable. The values making up the path were just the system assigned id for each item. I needed to sort on CategoryType and ItemName but I needed to keep parents and children together.

So I built the SortID field at the time that I unpivoted the #ValidPaths table into the ExpandedTree table with code like this:


create table #ExpandedTree
 (sortId       varchar(300),
  CatagoryType char(1), -- A character representing the catagory:
                        -- (L)ibrary, (P)roblem, (G)oal, (O)bjective, (I)ntervention
  IndentLevel  Int,     -- When displayed in the tree view the indention level of the item
  ItemName     varchar(50), -- for reasons that do not matter called wiley_<category>_id 
                            -- in the <catagory>_data tables.
  ItemText     varchar(5000))

insert into #ExpandedTree
 select Library_Name + 
        case when Problem_Name is not null then '.1.' + Problem_name else '' end +
        case when Goal_Name is not null then '.2.' + Goal_name else '' end +
        case when Objective_name is not null then '.3.' + Objective_Name else '' end +
        case when Intervention_name is not null then '.4.' + Intervention_name else '' end,
       'L', -- Library
       0, -- Libraries will always be at the top of path
       Library_Name,
       null -- Libraries only have names (no text)
  from ##ValidPaths
 where LIBID > 0
union
 select Library_Name + 
        case when Problem_Name is not null then '.1.' + Problem_name else '' end +
        case when Goal_Name is not null then '.2.' + Goal_name else '' end +
        case when Objective_name is not null then '.3.' + Objective_Name else '' end +
        case when Intervention_name is not null then '.4.' + Intervention_name else '' end,
        'P', -- Problem
        1, -- Problems will always be second on a path
        Problem_Name,
        convert(varchar(5000),Problem_Text) -- union statement makes me change ntext to varchar
  from ##ValidPaths
 where PROBID > 0
union …

… and that gave me id fields that look like this:


sortId
TestLinks3.1.A New Problem.2.Self Management.3.Groups.4.Community Intervention
TestLinks3.1.Productivity.3.Access
TestLinks3.1.Supportive Needs.2.Community.3.Access
TestLinks3.1.Supportive Needs.2.Community.3.Activities
TestLinks3.1.Supportive Needs.2.Social.3.Activities
TestLinks3.3.Activities

… Which sort in the order I want them to.

Now it is off to learn about reporting services and how to suppress redundant lines.

Thank You,

Laurie

Here is the beginning of my answer to you. It is probably a good thing I figured it out. I have a hard time being brief.

quote:
Originally posted by Peso

I still can't figure out why the "11.90..." group is sorted before the others

Hi Peso,

… and there in lies the problem. If it was straight forward I would not need to ask for help.

Ok, I have a tree where one path can be up to 5 levels (0 thru 4) deep. Any node can have 0 or more children (except Interventions, which will always have 0 children and Libraries which will have at least one child).

Nodes can be one of five categories: Library, Problem, Goal, Objective and Intervention.

Order Rules (by Level):
Level 0:
This Level only contains Libraries and they will be sorted by ItemName.

Level 1:
This Level contains all direct children of the given Libray. All category types (except Library) can be a direct child of a library.

This level will be sorted by Category (in the order listed above … which is not alphabetical and then by ItemName. The sort happens within each Parent of the sibling nodes.


Example:
Level 0    Level 1
Library: 1
           Problem: 1
           Problem: 2
	      Goal: 1
           Goal: 2
           Objective: 1
           Intervention: 1
Library: 2
         Problem: 1

Level 2
Same sort rules, but will only be a Goal, Objective or Intervention.


Example:
Level 0    Level 1          Level 2
Library 1
           Problem: 1
                            Goal: x
                            Goal: y
                            Objective: a
                            Intervention: a
           Problem: 2
           Goal: 1
           Goal: 2
           Objective: 1
           Intervention: 1

Level 3
Same as above, bill only have Objectives or Interventions.


Example:
Level 0    Level 1          Level 2            Level 3
Library 1
           Problem: 1
                            Goal: x
                                               Objective: m
                                               Objective: n
                                               Intervention: a
                            Goal: y
                            Objective: a
                                               Intervention: a
                            Intervention: a
           Problem: 2
           Goal: 1
           Goal: 2
           Objective: 1
           Intervention: 1

Level 4
This level if will only have Interventions and they should (within a given parent) be sorted by ItemName


Example:
Level 0    Level 1          Level 2            Level 3             Level 4 
Library 1
           Problem: 1
                            Goal: x
                                               Objective: m
                                                                   Intervention: a
                                                                   Intervention: c
                                               Objective: n
                                                                   Intervention: b
                                               Intervention: a
                            Goal: y
                            Objective: a
                                               Intervention: c
                            Intervention: a
           Problem: 2
           Goal: 1
           Goal: 2
           Objective: 1
           Intervention: 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources