CTE

Question

CTE

amanmeet.mehta 46966

SSC Eights!

Points: 859
More actions
May 14, 2012 at 1:36 pm

#268865

Hello,
I have a requirment where the hierarchy structure is defined in the below way.
QA Sponsor
QA Fund 1
QA Fund 2
QA Fund 3
QA Fund 4
QA Fund 5
QA Fund 1
Below is the proposed db design.
Child Id Level Parent
QA Sponsor1
QA Fund12QA Sponsor
QA Fund 23QA Fund1
QA Fund 34QA Fund 2
QA Fund 45QA Fund 3
QA Fund 52QA Sponsor
QA Fund12QA Fund 5
I have written the below CTE code and which provide me the o/p which is mentioned after the code.
;WITH cte AS
(
SELECT CAST('' + Name AS VARCHAR(100)) as 'Name', ID
FROM dbo.RELATION
WHERE PARENT_ID =''
UNION ALL
SELECT CAST(cte.Name + '--->' + t.Name AS VARCHAR(100)), t.ID
FROM dbo.RELATION t
INNER JOIN cte ON t.parent_id = cte.id
)
SELECT Name FROM cte
ORDER BY ID
O/P
---
QA SPONSOR
QA SPONSOR--->QA FUND1
QA SPONSOR--->QA FUND1--->QA FUND2
QA SPONSOR--->QA FUND1--->QA FUND2--->QA FUND3
QA SPONSOR--->QA FUND1--->QA FUND2--->QA FUND3--->QA FUND4
QA SPONSOR--->QA FUND5
QA SPONSOR--->QA FUND5--->QA FUND1
Everything looks ok however but since QA Fund 1 has fund 2,3,and 4 inside it. there should be one more line i.e. QA SPONSOR--->QA FUND5--->QA FUND1--->QA FUND2--->QA FUND3--->QA FUND4
I am stuck and not sure what should i do to get around this problem. Please help me in this.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

It would be quite a bit easier to help if you provided ddl and sample data. take a look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amanmeet.mehta 46966 SSC Eights! Points: 859 More actions · Answer 2

The structure is as follows/

QA Sponsor

-- Fund 1

--- Fund2

--- Fund3

--- Fund4

-- Fund 5

--- Fund 1

DDL

;WITH cte AS

(

SELECT CAST(” + Name AS VARCHAR(100)) as ‘Name’, ID

FROM dbo.RELATION

WHERE PARENT_ID =”

UNION ALL

SELECT CAST(cte.Name + ‘—>’ + t.Name AS VARCHAR(100)), t.ID

FROM dbo.RELATION t

INNER JOIN cte ON t.parent_id = cte.id

)

SELECT Name FROM cte

ORDER BY ID

O/P

—

QA SPONSOR

QA SPONSOR—>QA FUND1

QA SPONSOR—>QA FUND1—>QA FUND2

QA SPONSOR—>QA FUND1—>QA FUND2—>QA FUND3

QA SPONSOR—>QA FUND1—>QA FUND2—>QA FUND3—>QA FUND4

QA SPONSOR—>QA FUND5

QA SPONSOR—>QA FUND5—>QA FUND1

Everything looks ok however but since QA Fund 1 has fund 2,3,and 4 inside it. there should be one more line i.e. QA SPONSOR—>QA FUND5—>QA FUND1—>QA FUND2—>QA FUND3—>QA FUND4

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

That is not ddl and does not make anything easier. Create table scripts and sample data (insert statements).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amanmeet.mehta 46966 SSC Eights! Points: 859 More actions · Answer 4

Hello

Below are the DDL script and the data.

CREATE TABLE RELATION (IDINT,

NAMEVARCHAR(20),

PARENT_IDINT,

LEVELINT)

-- Insert scripts for the table

INSERT into RELATION VALUES ( 1,'QA SPONSOR','',1)

INSERT into RELATION VALUES ( 2,'QA FUND1','1',2)

INSERT into RELATION VALUES ( 3,'QA FUND2','2',3)

INSERT into RELATION VALUES ( 4,'QA FUND3','3',4)

INSERT into RELATION VALUES ( 5,'QA FUND4','4',5)

INSERT into RELATION VALUES ( 6,'QA FUND5','1',2)

INSERT into RELATION VALUES ( 7,'QA FUND1','6',3)

Thanks for your help

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Thanks that helps to see what the issue a ton!!!

I don't see how you would get the "missing" row. What you have coded is exactly what the data says should be there. I think you are saying that you somehow think that QA Fund 1 should have more than 1 parent? You did notice that you two rows with a name of QA Fund 1? The second one (ID 7) is the one showing up under QA Fund 5.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amanmeet.mehta 46966 SSC Eights! Points: 859 More actions · Answer 6

Thanks for your reply.

Yes Fund 1 is twice as it is under QA sponsor and Fund 5. However Fund 1 has Fund 2 which has fund 3 and 4 within it.

So the expectation is that where ever fund 1 is there we should display its child i.e. fund 2 3 4. The missing row which is need is for that.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

You don't have any missing rows.

Take a look at this, it will show the ID of the children.

;WITH cte AS

(

SELECT CAST('' + Name AS VARCHAR(100)) as 'Name', ID

FROM dbo.RELATION

WHERE PARENT_ID =''

UNION ALL

SELECT CAST(cte.Name + '->' + t.Name + ' (' + cast(t.id as CHAR(1)) + ')' AS VARCHAR(100)), t.ID

FROM dbo.RELATION t

INNER JOIN cte ON t.parent_id = cte.id

)

SELECT Name, ID FROM cte

ORDER BY ID

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amanmeet.mehta 46966 SSC Eights! Points: 859 More actions · Answer 8

Here if you see fund1 has fund 2 3 4 within it so you see this QA SPONSOR->QA FUND1 (2)->QA FUND2 (3)->QA FUND3 (4)->QA FUND4 (5)

but now under fund 5 you just see QA SPONSOR->QA FUND5 (6)->QA FUND1 (7) we need to display fund 2 3 4 also under it.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

That is because you don't have data for that. Your second QA Fund 1 is from ID 7. There is nothing that says it has a parent of QA Fund 2 (ID 3).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amanmeet.mehta 46966 SSC Eights! Points: 859 More actions · Answer 10

Sorry for being a pain so what entry do i need to put to get this resolved?

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

amanmeet.mehta 46966 (5/14/2012)
Sorry for being a pain so what entry do i need to put to get this resolved?

You aren't being a pain. 😛 I think you need to build the next tree.

insert Relation

select 8, 'QA Fund2', 7, 4 union all

select 9, 'QA Fund3', 8, 5 union all

select 10, 'QA Fund4', 9, 6

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amanmeet.mehta 46966 SSC Eights! Points: 859 More actions · Answer 12

i thought about this option of inserting but my application is not doing it. so instead of inserting 3 lines what if i insert INSERT into RELATION VALUES ( 2,'QA FUND1','6',3,7)

I am able to get the desired result set but i am not sure how will i sort it. below is the result set which i get

QA SPONSOR

QA SPONSOR--->QA FUND1

QA SPONSOR--->QA FUND5--->QA FUND1--->QA FUND2

QA SPONSOR--->QA FUND1--->QA FUND2

QA SPONSOR--->QA FUND1--->QA FUND2--->QA FUND3

QA SPONSOR--->QA FUND5--->QA FUND1--->QA FUND2--->QA FUND3

QA SPONSOR--->QA FUND5--->QA FUND1--->QA FUND2--->QA FUND3--->QA FUND4

QA SPONSOR--->QA FUND1--->QA FUND2--->QA FUND3--->QA FUND4

QA SPONSOR--->QA FUND5

QA SPONSOR--->QA FUND5--->QA FUND1

Result set needed

QA SPONSOR

QA SPONSOR--->QA FUND1

QA SPONSOR--->QA FUND1--->QA FUND2

QA SPONSOR--->QA FUND1--->QA FUND2--->QA FUND3

QA SPONSOR--->QA FUND1--->QA FUND2--->QA FUND3--->QA FUND4

QA SPONSOR--->QA FUND5

QA SPONSOR--->QA FUND5--->QA FUND1

QA SPONSOR--->QA FUND5--->QA FUND1--->QA FUND2

QA SPONSOR--->QA FUND5--->QA FUND1--->QA FUND2--->QA FUND3

QA SPONSOR--->QA FUND5--->QA FUND1--->QA FUND2--->QA FUND3--->QA FUND4

Any thoughts how can i sort it

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Just sort by the "Name" column that you built up instead of the ID.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)