July 3, 2012 at 10:12 am
I'm trying to use a CTE recursive query to return multi-level data stored in a single table.
RECORD = highest level; collection of fields and segments
SEGMENT = middle level; collection of fields and segments
FIELD = lowest level
Example:
OWNER TYPE TYPE REF
REC1 RECORD FIELD FLD1
REC1 RECORD FIELD FLD2
REC1 RECORD SEGMENT SEG1
SEG1 SEGMENT FIELD FLD3
SEG1 SEGMENT FIELD FLD4
SEG1 SEGMENT SEGMENT SEG2
SEG2 SEGMENT FIELD FLD5
SEG2 SEGMENT SEGMENT SEG3
... ... ... ...
What I want to return are all of the FIELD types. I think I need recursion though since the table is self-referential.
Can this be done with a CTE? I haven't had any luck so far based on examples I've seen online.
john
July 3, 2012 at 10:15 am
Hi and welcome to SSC! This is certainly possible but not without some details from your end. We need ddl, sample data and desired output. See 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/
July 3, 2012 at 10:31 am
Here is some sample code:
if object_id('#recursive_example','U') is not null
drop table #recursive_example
create table #recursive_example (
owner varchar(10),
owner_type varchar(10),
item_type varchar(10),
item_name varchar(10)
)
insert into #recursive_example values ('REC1','RECORD','FIELD','FLD1')
insert into #recursive_example values ('REC1','RECORD','FIELD','FLD2')
insert into #recursive_example values ('REC1','RECORD','SEGMENT','SEG1')
insert into #recursive_example values ('SEG1','SEGMENT','FIELD','FLD3')
insert into #recursive_example values ('SEG1','SEGMENT','FIELD','FLD4')
insert into #recursive_example values ('SEG1','SEGMENT','SEGMENT','SEG2')
insert into #recursive_example values ('SEG2','SEGMENT','FIELD','FLD5')
insert into #recursive_example values ('SEG2','SEGMENT','SEGMENT','SEG3')
insert into #recursive_example values ('SEG3','SEGMENT','FIELD','FLD6')
insert into #recursive_example values ('SEG3','SEGMENT','FIELD','FLD7')
insert into #recursive_example values ('REC2','RECORD','FIELD','FLD8')
insert into #recursive_example values ('REC2','RECORD','FIELD','FLD9')
insert into #recursive_example values ('REC2','RECORD','SEGMENT','SEG1')
select *
from #recursive_example
I would like to return all of the item_typ = FIELD for owner_type = RECORD.
REC1, FLD1
REC1, FLD2
REC1, FLD3
REC1, FLD4
REC1, FLD5
REC1, FLD6
REC1, FLD7
REC2, FLD8
REC2, FLD9
REC2, FLD3
REC2, FLD4
REC2, FLD5
REC2, FLD6
REC2, FLD7
I hope that is more clear than my first attempt.
July 3, 2012 at 11:05 am
That certainly helps but it is awfully confusing. You seem to have a mix between an EAV type of structure and an adjacency list. I have to admit that this is making my eyes cross a bit. I don't quite understand the data and the relationship with the other rows.
_______________________________________________________________
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/
July 3, 2012 at 11:17 am
Right, it's not well structured. It is not really designed for SQL to spin through - the application code that sits on top of it sorts it out. However, I have need to include this in some T-SQL.
Think of REC1 as a table definition. SEG1 is a collection of sub segments or fields. A segment will always lead to a field. And a field is the ending node of the recursive tree.
Here's one way to visualize REC1 in the example.
REC1
-->FLD 1-2
-->SEG1
--->FLD 3-4
--->SEG2
---->FLD 5
---->SEG3
----->FLD 6-7
Ultimately, REC1 is defined by fields 1-7.
July 3, 2012 at 11:24 am
I was having a discussion on a different thread and this seems to work. I have to try this on real data, but it works here...
with cte as (
select [owner] as StartOwner, item_name, item_type, [owner]
from recursive_example r
where r.owner_type = 'RECORD'
union all
select StartOwner, r.item_name, r.item_type, r.owner
from recursive_example r
inner join cte on cte.item_name = r.owner
)
select StartOwner as owner, item_name
from cte where item_type = 'FIELD'
order by owner, item_name
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply