CODEWITHSUNDEEP
sqlsql-server-2008group-bycommon-table-expressionfor-xml-path
User Learning
User Learning

Reputation: 3513

SQL XML path conversion results error

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 ?

Upvotes: 2

Views: 396

Answers (2)

lc.
lc.

Reputation: 116538

The problem has nothing to do with your CTE. It actually lies in the following subquery:

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('')

Note how you are using the aggregate COUNT(*) here as well as the column Chars. You need to group by at least Chars here:

SELECT (Cast(Chars as varchar(1)) + 
    ' appears (' + 
    cast(COUNT(*) as varchar(5)) + 
    ' ) times') AS [data()] 
FROM Ctea t2 
WHERE t2.Data = t1.data 
GROUP BY t2.Chars
FOR XML PATH('')

Furthermore, you do not want to select or group by t1.Chars in the outer query because it will result in one row per value of Chars:

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

Finally you should most likely be using the STUFF function, and not REPLACE, as you are trying to create a space-delimited list ("1 appears (1) times 2 appears (1) times"), not replace all space characters with a space and a semicolon ("1 ;appears ;(1) ;times ;2 ;appears ;(1) ;times").

Therefore your final query should be:

; 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,
    STUFF((SELECT cast(' ' as varchar(max)) + (Cast(Chars as varchar(1)) + ' appears (' + cast(COUNT(*) as varchar(5))+ ' ) times') AS [data()] 
           FROM Ctea t2 
           WHERE t2.Data = t1.data 
           GROUP BY t2.Chars 
           FOR XML PATH('')), 1, 1, '') As Number_of_occurances  
from ctea as t1
group by t1.data

Upvotes: 2

ughai
ughai

Reputation: 9890

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

Upvotes: 0

Related Questions