SQL Server recursive CTE and pivot don't work together

Question

Why doesn't this SQL Fiddle work?

Full script replicated:

create table tbl (
  id int,
  month varchar(9),
  value float);
insert tbl values
(1,'Jan',0.12),
(1,'Feb',0.36),
(1,'Mar',0.72),
(2,'Mar',0.11),
(2,'Apr',0.12),
(2,'May',0.36);

declare @tbl table (
  id int,
  number int,
  month varchar(9),
  value float);
insert @tbl
select id.id, Months.Number, Months.Name, t.value
from (values(1,'Jan'),
            (2,'Feb'),
            (3,'Mar'),
            (4,'Apr'),
            (5,'May'),
            (6,'Jun')) Months(Number,Name)
cross join (select distinct id from tbl) id
left join tbl t on t.month = Months.name and t.id=id.id;

;with cte as (
  select id,Number,month,isnull(Value,0.0)value
  from @tbl
  where Number=1
  union all
  select cte.id,cte.Number+1,cte.month,isnull(t.value,cte.Value)
  from cte
  join @tbl t on t.id=cte.id and t.number=cte.number+1
)
/*update t
set value=cte.value
from @tbl t
join cte on t.id=cte.id and t.number=cte.number;*/

select id, Jan,Feb,Mar,Apr,May,Jun
from (select id,month,value from /*@tbl*/ cte) p
pivot (max(value) for month in (Jan,Feb,Mar,Apr,May,Jun)) v;

Expected result:

ID  JAN FEB MAR APR MAY JUN
1   0.12    0.36    0.72    0.72    0.72    0.72
2   0   0   0.11    0.12    0.36    0.36

Actual result:

ID  JAN FEB MAR APR MAY JUN
1   0.72    (null)  (null)  (null)  (null)  (null)
2   0.36    (null)  (null)  (null)  (null)  (null)

If you uncomment the commented-out code, it works. However, if you select from the CTE directly SELECT * FROM CTE, it shows the same values that are in @tbl after the UPDATE statement.

I spent time analyzing CTE + ROW_NUMBER() a while back but hoping someone could explain this one.

score 2 · Accepted Answer · answered Oct 03 '12 at 21:04

2

I don't get the same results from the CTE that i get from @tbl. For the CTE all the months are JAN. If you change your CTE definition with this:

;with cte as (
  select id,Number,month,isnull(Value,0.0)value
  from @tbl
  where Number=1
  union all
  select cte.id,cte.Number+1,t.month /*here there was cte.month*/,
         isnull(t.value,cte.Value) 
  from cte
  join @tbl t on t.id=cte.id and t.number=cte.number+1
)

Then I get the same results.

answered Oct 03 '12 at 21:04

Lamak

69,480
12
108
116

Classic PEBKAC. Stared at it too long and it turned into starfields... thanks for the help. FYI that query was used here http://stackoverflow.com/a/12716649/573261 and I thought I uncovered another CTE "feature" – RichardTheKiwi Oct 03 '12 at 21:14

SQL Server recursive CTE and pivot don't work together

1 Answers1