Trouble with a CTE Hierarchy - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Trouble with a CTE Hierarchy

Author

Topic

skillilea
Starting Member

15 Posts

Posted - 2012-02-06 : 00:28:50

I'm having trouble ordering the CTE results.

If you look at the data I need all the:

Business Sys ( parent entity 180) to fall under the Business systems eLevel 1. I am trying to build the eGroupBy with no luck.

Any help:

--CTE
;WITH eHierarchy (eLevel, entityID, eTypeID, eParentEntityID, eGroupBy)
AS
(
SELECT
0 eLevel
, e.entityID entityID
, e.eTypeid eTypeID
, e.eParentEntityID eParentEntityID
,CAST( '\' AS varchar(max)) eGroupBY -- CAST( e.entityID AS varchar(max)) +'\'

FROM InnoCommon.dbo.tblEntity e
WHERE e.eParentEntityID IS NULL

UNION ALL

SELECT
eh.eLevel + 1 eLevel
, ce.entityID entityID
, ce.eTypeid eTypeid
, ce.eParentEntityID eParentEntityID
, CAST( eh.eParentEntityID AS varchar(max)) +'\'+ CAST( ce.eParentEntityID AS varchar(max) ) eGroupBY

FROM InnoCommon.dbo.tblEntity ce
INNER JOIN eHierarchy eh ON eh.entityID = ce.eParentEntityID
WHERE ce.eTypeID IN( 12,4,11)
AND ce.eIsActive =1
)

SELECT
h.entityID
,h.eLevel
,e.eOrder
,e.eLName
,e.eParentEntityID
,h.eGroupBy
FROM eHierarchy h
INNER JOIN InnoCommon.dbo.tblEntity e ON (h.entityID = e.entityID)

WHERE h.eLevel > 0
ORDER BY h.eGroupBy

--Results

entityID eLevel eOrder eLname eParentEntityID eGroupBy
180 1 1 Business Systems 1 NULL
181 1 2 Operations 1 NULL
182 1 8 Stewardship 1 NULL
183 1 3 CAM 1 NULL
184 1 4 Finance 1 NULL
185 1 5 People 1 NULL
186 1 6 Quality 1 NULL
187 1 7 Technical 1 NULL
188 2 1 1st Impress. Club 180 1\180
199 2 1 Corporate Responsibility 180 1\180
200 2 1 Corporate Direction 180 1\180
202 2 1 Disaster Planning 180 1\180
211 2 1 InnoGear 180 1\180
213 2 1 Information Technology 180 1\180
216 2 1 Library 180 1\180
217 2 1 Lunch Program 180 1\180
223 2 1 Office Supplies 180 1\180
235 2 1 Research and Development 180 1\180
245 2 1 5lb Bag 180 1\180
264 2 1 Travel 180 1\180
265 2 1 Marketing 180 1\180
266 2 1 Public Relations 180 1\180
270 2 1 Organizational Development 180 1\180

thanks for the look

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 09:32:14

ORDER BY COALESCE(eParentEntityID,entityID),eLevel

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources