CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
user3801124
user3801124

Reputation: 21

CTE Pivot in sql server

I have been given a task to complete and frustrated with the syntax on this one,

Our db in SQL Server has a ragged hierarchy with 6 columns, i have created some insert code to get it perfect.

Desired output is to find the top layer for each CostCentreID that is under 100 - and to skip out any layers where the isservice and issector are both SET to 0, you can see in my desired output ignoreme and corp services rows should not come out - and i need it pivoted horizontally so the CostCentres are along the top.

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)

Desired output:

Parent1,           Parent2,                      Parent 3,            Parent4
Legal         ML Corporate Services         Corporate Services         xxxxxetc

Annoyingly each heirarchy has a different number of potential parents - I think the maximum is 9 parents.

Can someone help me to pivot the data and figure this out?

Regards

J

Hi there

I am 99% there Jayvee thankyou for your help.

My output is as follows:

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

The desired line is:

88,10289,10292 - because 10291, and 10000 both have 0,0 for isservice and issector

How do i still count them as part of the heirarchy ladder when calculating it, but not output them? Basically i want the highest Sector OR Service to be the last item in each row

Upvotes: 2

Views: 215

Answers (2)

Jayvee
Jayvee

Reputation: 10875

Another option to consider is a string builder with CTE, something like this:

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)

Upvotes: 0

Andomar
Andomar

Reputation: 238076

You can left join nine times:

select  child
,       parent1.name
,       parent2.name
,       parent3.name
...
from    YourTable child
left join
        YourTable parent1
on      parent1.ID = child.ParentID
left join
        YourTable parent2
on      parent2.ID = parent1.ParentID
left join
        YourTable parent3
on      parent3.ID = parent2.ParentID
....

Upvotes: 1

Related Questions