1

I stumbled on a problem when I try to use CTE with GROUP BY.

I need to concatenate strings in a 'group by' manner, that is, from the table: 

   id value
   --------
    1    a
    1    b
    2    c
    2    d

I want to get the following result: 

id vals
1  a,b
2  c,d

The following query will give the desired result([How to use GROUP BY to concatenate strings in SQL Server?)

create table #B 
(
     id int, 
     value varchar(8)
);

insert into #B (id, value) 
values (1, 'a'), (1, 'b'), (2, 'c'), (2, 'd');

select 
    id, 
    stuff((select distinct ','+value 
           from #B 
           where id = a.id 
           for xml path ('')),1,1,'') as vals 
from #B as a
group by id;

But if I replace the table #B with a trivial CTE:

with A as 
(
     select * from #B
)
select 
    id, 
    stuff((select distinct ',' + value 
           from A 
           where id = a.id 
           for xml path ('')),1,1,'') as vals 
from A as a 
group by id

SQL Server returns a wrong result without any warning:

id vals
----------
1  a,b,c,d
2  a,b,c,d

Can anyone explain how the CTE query above comes up the wrong result? It is interesting to know how the CTE above is interpreted by SQL Server. Thank you.

Community
  • 1
  • 1
sgu
  • 1,301
  • 1
  • 13
  • 25

1 Answers1

3

You have used alias a which refers to your CTE just change it and have a fun. I mean here id=a.id a refers to itself.

with A as (
select * from #B
)
select id, 
    stuff((select distinct ','+value from A where id=b.id for xml path ('')),1,1,'') as vals 
from A as b 
group by id
Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
  • Oops, forgot sql is case-insensitive. That's what happens when working with multiple languages at the same time on a long day. – sgu Aug 05 '15 at 00:28