Geek Answers Handbook

Porting PostgreSQL string_agg to SQL Server, ORDER BY problem

PostgreSQL has this very useful string_agg function that allows you to query:

SELECT
    contacts.first_name,
    contacts.last_name,
    (
        SELECT
            string_agg(number, ', ' ORDER BY phones.priority)
        FROM
            phones
        WHERE
            phones.contact_id = contacts.id
    ) AS phone_numbers
FROM
    contacts

Similarly, this can be done in MySQL using group_concat.

Now I'm trying to port this to SQL Server as a custom CLR aggregate. The code itself is not a problem, since there are thousands of examples throughout the website to create this particular aggregate (which ask the question: why is it not part of SQL Server already?).

The problem is that I cannot find a clean way to use ORDER BY, because SQL Server not only does not support ORDER BY as part of the aggregate function, but also prohibits the use of ORDER BY in a subquery. My best choice:

SELECT
    contacts.first_name,
    contacts.last_name,
    (
        SELECT
            dbo.string_agg(number, ', ')
        FROM
            (
                SELECT TOP <some really large number>
                    number
                FROM
                    phones
                WHERE
                    phones.contact_id = contacts.id
                ORDER BY
                    phones.priority
            ) AS phones
    ) AS phone_numbers
FROM
    contacts

? , , , , ORDER BY .

+5
2

, , TOP <some really large number>, SQL Server:

SELECT
    contacts.first_name,
    contacts.last_name,
    (
        SELECT
            dbo.string_agg(number, ', ')
        FROM
            (
                SELECT TOP <some really large number>
                    number
                FROM
                    phones
                WHERE
                    phones.contact_id = contacts.id
                ORDER BY
                    phones.priority
            ) AS phones
    ) AS phone_numbers
FROM
    contacts
0

, . ORDER BY TOP , ORDER BY . .

0

More articles:


All Articles