locked
CTE with pivotting RRS feed

  • Question

  • Our db in SQL Server has a ragged heirarchy with 6 columns,

    i have created some insert code to get it perfect.

    Desired output is to find the top layer for each CostCentreid and not output any layers

    where the isservice and issector are both SET to 0, but still consider them part of the ladder

    So if a 0,0 appears for these items i still want it to continue climbing the heirarchy to find the highest non 0,0 in that ladder.

    here is a test sql script:

    CREATE table #test

    ( ParentID int, CostCentre varchar(max), CostCentreID int, CostCentreCode varchar(max), ISService bit, ISSector bit ) GO INSERT into #test VALUES(10289,'Legal',88,'0107',0,0) INSERT into #test VALUES(10291,'ML Corp',10289,'MLCorpServ',0,1) INSERT into #test VALUES(10292,'IgnoreMe',10291,'MLCorpServ',0,0) INSERT into #test VALUES(10000,'Corp Services',10292,'CorpSvcs',1,0) INSERT into #test VALUES(10000,'Corp Services Top',10000,'CorpSvcstop',0,0)

    And the query:

    WITH r AS ( SELECT CAST(CostCentreid AS VARCHAR(max)) + ',' + CAST(parentid AS VARCHAR(max)) sb FROM #test UNION ALL SELECT sb +',' + CAST(parentid AS VARCHAR(max)) FROM r JOIN #test t ON rtrim(CAST(t.costcentreid AS VARCHAR(max)))=rtrim(right(r.sb,CHARINDEX(',',reverse(r.sb))-1)) WHERE t.parentid<>t.costcentreid ) SELECT * FROM r OPTION (MAXRECURSION 0)



    Which outputs:

    88,10289
    88,10289,10291
    88,10289,10291,10292
    88,10289,10291,10292,10000

    But my desired output is only ONE ROW -

    88,10289,10292 because 10291 and 10000 are both not a sector OR a Sector (0,0) but it needs to ignore 10291 even though its a 0,0 as there is a higher level with a sector

    Also i cant figure out how to add additional columns from the table, i want to display the CostCentreName in the next column

    Can someone help me out with this ? thanks :) J



    • Edited by James OHara Thursday, July 3, 2014 3:09 PM missed a bit more
    Thursday, July 3, 2014 3:06 PM

Answers

  • One way

    ;WITH r AS 
    ( SELECT CAST(CostCentreid AS VARCHAR(max)) sb,--+ ',' + CAST(parentid AS VARCHAR(max)) sb,
       CostCentre, ParentID, 1 As Level, ISService, ISSector, CostCentreid, CostCentre As StartCostCentre, CostCentreID As StartCostCentreID
    FROM #test
    UNION ALL 
    SELECT sb + CASE WHEN t.ISService <> 0 Or t.ISSector <> 0 Then ',' + CAST(r.parentid AS VARCHAR(max)) Else '' End,
       t.CostCentre, t.ParentID, Level+1 As Level, t.ISService, t.ISSector, t.CostCentreID, r.StartCostCentre, r.StartCostCentreID 
    FROM r 
    JOIN #test t ON t.costcentreid = r.ParentID 
    WHERE r.parentid<>r.costcentreid ),
    cteOrdered As
    (Select sb, StartCostCentre, StartCostCentreID, 
      Row_Number() Over(Partition By StartCostCentre Order By Level Desc) As rn
    From r
    Where ISService <> 0 Or ISSector <> 0) 
    SELECT sb, StartCostCentre As CostCentre FROM cteOrdered
    Where rn = 1 And StartCostCentreID Not In (Select t.ParentID From #test t)
    Order By sb
    OPTION (MAXRECURSION 0)

    Tom

    • Proposed as answer by Kalman Toth Friday, July 4, 2014 12:18 AM
    • Marked as answer by Fanny Liu Thursday, July 10, 2014 9:18 AM
    • Unmarked as answer by James OHara Sunday, July 13, 2014 1:00 AM
    • Marked as answer by James OHara Sunday, July 13, 2014 1:01 AM
    Thursday, July 3, 2014 4:07 PM