concatenate string inside sql recursive WITH statement CTE

Question

i'm trying to concatenate the values from a column from all levels of a certain path

this is my sql:

WITH
hi as (
select c.id id, cast(c.code as nvarchar) code, c.title, c.parent from CaseTypes c 
where c.parent is null

union all

select c.id, cast((h.code + c.code ) as nvarchar) code , c.title, c.parent from CaseTypes c 
inner join hi h on c.parent = h.id
)
select * from hi

the problem is that only the first level (where parent is null) is taken, the rest isn't

I just ran your exact code on a test table and it seems to work fine. — Derek
– Derek, Commented Jul 27, 2011 at 14:40

Wallace · Accepted Answer · 2020-06-19 19:05:56Z

6

This code will concatenate the string and only display the last parent

declare @CaseTypes table (
    code nvarchar(max),
    title varchar(20),
    parent int, id int
)

insert @CaseTypes values ('a', 't1', null, 1)
insert @CaseTypes values ('b', 't2',    1, 2)
insert @CaseTypes values ('c', 't3',    2, 3)

;with hi as (

    select c.id id, cast(c.code as nvarchar) code, c.title, c.parent 
    from @CaseTypes c 
    where c.parent is null

    union all

    select c.id, cast((h.code + c.code ) as nvarchar) code, c.title, c.parent
    from @CaseTypes c 
    inner join hi h on c.parent = h.id
)

select id, code, title, parent 
from hi h 
where not exists (
    select 1
    from hi
    where h.id = parent
)

edited Jun 19, 2020 at 19:05

Wallace

18k9 gold badges62 silver badges86 bronze badges

answered Jul 27, 2011 at 15:16

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

t-clausen.dk Over a year ago

Not really much change, but sometimes the problems we are faceing are hard to spot.

Omu Over a year ago

it's no change at all actually, but at least I know that the problem is in something else

t-clausen.dk Over a year ago

there is a change, I picked the right row. I was assuming your code only picked one of the rows from the result

Omu Over a year ago

my problem is in the concatenation, in your example if doing select * at the end you will get for code a, ab, abc (3 rows) as with my table i get just 'a' (code of the first parent) for all the rows

t-clausen.dk Over a year ago

I took your exact code and tried, I get a,ab,abc with your exact code. I think you should take a look at your id and parent for your data, I bet the problem is there.

Collectives™ on Stack Overflow

concatenate string inside sql recursive WITH statement CTE

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related