SQL XML path conversion results error

Question

Actually I m begineer to SQL XML path as so making me professional, Got a scenario... I have a CTE Function That results as

Data    Chars   NumberOfOccurance
12  1   1 appears (1 ) times
12  2   2 appears (1 ) times
xx  x   x appears (2 ) times

and CTE function is :

  ;with cte as
    (
        select Data , SUBSTRING(Data,1,1) as Chars,1 as startpos from @t
        union all
        select Data, SUBSTRING(Data, startpos+1,1) as char,startpos+1 from cte where startpos+1<=LEN(data)
    )
    select Data,Chars,Cast(Chars as varchar(1)) + ' appears (' + cast(COUNT(*) as varchar(5))+  ' ) times' as 'NumberOfOccurance' from cte 
    group by data, chars

Actually I just want to make my answer into this :

data    Number_of_occurances
12  1 appears (1) times 2 appears (1) times
xx  x appears (2) times

I have tries this :

; With Ctea as 
(
select Data , SUBSTRING(Data,1,1) as Chars,1 as startpos from @t
    union all
    select Data, SUBSTRING(Data, startpos+1,1) as char,startpos+1 from ctea where startpos+1<=LEN(data)
)
select Data,Chars,REPLACE((SELECT (Cast(Chars as varchar(1)) + ' appears (' + cast(COUNT(*) as varchar(5))+ ' ) times') AS [data()] FROM Ctea t2 WHERE t2.Data = t1.data FOR XML PATH('')), ' ', ' ;') As Number_of_occurances  from ctea as t1
group by t1.data, t1.Chars

It says :

Column 'Ctea.Chars' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

when I use temp table and getting my exact answer , but cant do it CTE

Can anyone make my result ?

Answer 1

You can use FOR XML PATH like this for concatenation

  ;with cte as
    (
        select Data , SUBSTRING(Data,1,1) as Chars,1 as startpos from @t
        union all
        select Data, SUBSTRING(Data, startpos+1,1) as char,startpos+1 from cte where startpos+1<=LEN(data)
    ), CTE2 AS
    (
    select Data,Chars,Cast(Chars as varchar(1)) + ' appears (' + cast(COUNT(*) as varchar(5))+  ' ) times' as 'NumberOfOccurance' from cte 
    group by data, chars
    )
    SELECT Data,(SELECT NumberOfOccurance + ' ' FROM CTE2 c2 WHERE c2.Data = C1.Data FOR XML PATH(''),type).value('.','VARCHAR(MAX)') as Number_of_occurances 
    FROM CTE2 C1
    GROUP BY Data