T-sql Common expression query as subquery

Posted by ase69s on Stack Overflow See other posts from Stack Overflow or by ase69s
Published on 2010-03-31T09:06:58Z Indexed on 2010/03/31 9:13 UTC
Read the original article Hit count: 640

I have the following query:

    WITH Orders(Id)
AS (
SELECT DISTINCT anfrageid FROM MPHotlineAnfrageAnhang
)
SELECT Id,
(
SELECT CONVERT(VARCHAR(255),anfragetext) + ' | '
FROM MPHotlineAnfrageAnhang
WHERE anfrageid = Id
ORDER BY anfrageid, erstelltam
FOR XML PATH('')
) AS Descriptions
FROM Orders

Its concatenates varchar values of diferents rows grouped by an id. But now i want to include it as a subquery and it gives some errors i cant solve. Simplified example of use:

select descriptions from 
(
    WITH Orders(Id)
    AS (
    SELECT DISTINCT anfrageid FROM MPHotlineAnfrageAnhang
    )
    SELECT Id,
    (
    SELECT CONVERT(VARCHAR(255),anfragetext) + ' | '
    FROM MPHotlineAnfrageAnhang
    WHERE anfrageid = Id
    ORDER BY anfrageid, erstelltam
    FOR XML PATH('')
    ) AS Descriptions
    FROM Orders
) as tx where id=100012

Errors (Aproximate translation from spanish):

-Incorrect sintaxis near 'WITH'.
-Incorrect sintaxis near 'WITH'. If the instruction is a common table expression or a xmlnamespaces clause, the previous instruction must end with semicolon.
-Incorrect sintaxis near ')'.

What im doing wrong?

© Stack Overflow or respective owner

Related posts about tsql

Related posts about common-table-expression