How to concatenate rows delimited with comma using standard SQL?

Question

Let's suppose we have a table T1 and a table T2. There is a relation of 1:n between T1 and T2. I would like to select all T1 along with all their T2, every row corresponding to T1 records with T2 values concatenated, using only SQL-standard operations.

Example: T1 = Person T2 = Popularity (by year)

for each year a person has a certain popularity

I would like to write a selection using SQL-standard operations, resulting something like this:

Person.Name    Popularity.Value
John Smith     1.2,5,4.2
John Doe       NULL
Jane Smith     8

where there are 3 records in the popularity table for John Smith, none for John Doe and one for Jane Smith, their values being the values represented above. Is this possible? How?

I'm using Oracle but would like to do this using only standard SQL.

Don't think you can achieve it in pure SQL92 without resorting to vendor-specific tricks. Want an Oracle-only solution? — David Jashi, Jun 25 '13 at 21:24
possible duplicate of [How can I combine multiple rows into a comma-delimited list in Oracle?](http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle) — dang, Jun 25 '13 at 21:26
I want to achieve this in Oracle, but with SQL-standard operations. — Lajos Arpad, Jun 25 '13 at 21:28
@DavidJashi: Isn't it possible via a recursive common table expression? It's SQL standard, but SQL99, I believe. It's generally much slower than the vendor-specific methods. — voithos, Jun 25 '13 at 21:29
Dang, I have seen that, but there is an oracle-specific solution. I would like to solve that with SQL-standard operations. — Lajos Arpad, Jun 25 '13 at 21:29
Voithos, that's right, but an Oracle-specific solution cannot be migrated. — Lajos Arpad, Jun 25 '13 at 21:30
@DavidJashi Can you please specify level of SQL standard which you want to follow? Just for reference: http://en.wikipedia.org/wiki/SQL#Standardization Concatenation are possible, but only if database supports one of SQL standards modern enough. — ThinkJet, Jun 25 '13 at 21:38
@Bluefeet, on the link you have mentioned when you edited my question we can find Oracle, MS SQL solutions, but at least I did not find any solution in conform with SQL-standard — Lajos Arpad, Jun 25 '13 at 21:50
Also, the title of that question is How can I combine multiple rows into a comma-delimited list in Oracle? This specifies the vendor. I did not specify the vendor, just added a tag to the question to show that I will implement this in Oracle. But in the question I explicitly specified I would like to solve this in a technology-agnostic way, mentioning that I would like to have a solution in conform with SQL-standard. I have already solved this in Oracle-specific way, so the answers in the question you have mentioned are not answering this question, so this is not a duplicate of that. — Lajos Arpad, Jun 25 '13 at 21:57
@Lajos I didn't post a link, I edited your question to remove the query tag. — Taryn, Jun 25 '13 at 22:01
Then somebody else edited the question to add that "This question may already...". Sorry, I believed it was you and wanted to prove that this question does not have an answer in the given link. — Lajos Arpad, Jun 25 '13 at 22:04
@DavidJashi: In case you're curious, I posted an example recursive query. — voithos, Jun 25 '13 at 22:37

voithos · Answer 1 · 2013-06-25T22:41:29.170

Here's one technique, using recursive Common Table Expressions. Unfortunately, I'm not confident on its performance.

I'm sure that there are ways to improve this code, but it shows that there doesn't seem to be an easy way to do something like this using just the SQL standard.

As far as I can see, there really should be some kind of STRINGJOIN aggregate function that would be used with GROUP BY. That would make things like this much easier...

This query assumes that there is some kind of PersonID that joins the two relations, but the Name would work too.

WITH cte (id, Name, Value, ValueCount) AS (
    SELECT id,
        Name,
        CAST(Value AS VARCHAR(MAX)) AS Value,
        1 AS ValueCount
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS id,
            Name,
            Value
        FROM Person AS per
        INNER JOIN Popularity AS pop
            ON per.PersonID = pop.PersonID
    ) AS e
    WHERE id = 1

    UNION ALL

    SELECT e.id,
        e.Name,
        cte.Value + ',' + CAST(e.Value AS VARCHAR(MAX)) AS Value,
        cte.ValueCount + 1 AS ValueCount
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS id,
            Name,
            Value
        FROM Person AS per
        INNER JOIN Popularity AS pop
            ON per.PersonID = pop.PersonID
    ) AS e
    INNER JOIN cte
        ON e.id = cte.id + 1
            AND e.Name = cte.Name
)
SELECT p.Name, agg.Value
FROM Person p
LEFT JOIN (
    SELECT Name, Value
    FROM (
        SELECT Name,
            Value,
            ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ValueCount DESC)AS id
        FROM cte
    ) AS p
    WHERE id = 1
) AS agg
    ON p.Name = agg.Name

This is an example result:

--------------------------------
| Name        | Value          |
--------------------------------
| John Smith  | 1.2,5,4.2      |
--------------------------------
| John Doe    | NULL           |
--------------------------------
| Jane Smith  | 8              |
--------------------------------

Thank you, voithos, I will implement your idea soon and will let you know about the results. If it is conform SQL standard and works, I will accept it. Thanks so much. — Lajos Arpad, Jun 25 '13 at 22:35
@LajosArpad: Please, be careful about performance. If you implement it, be sure to test using realistic dataset sizes. — voithos, Jun 25 '13 at 22:37
I had just come up with something similar and the process of writing the code showed me how pointless it is to write 'database-agnostic' code. Even something as simple as creating test data with 'from dual' or concatenating strings with '||', '+' or 'concat' shows how difficult it is. Way more trouble than it's worth! — Mike Meyers, Jun 25 '13 at 22:40
@MikeMeyers: I agree - I think things like this are much easier to handle in application code. And if you *really* want to be database agnostic, use an ORM, or some other kind of abstraction. — voithos, Jun 25 '13 at 22:43
ORA-32033: unsupported column aliasing 32033. 00000 - "unsupported column aliasing" *Cause: column aliasing in WITH clause is not supported yet *Action: specify aliasing in defintion subquery and retry Error at Line: 10 Column: 10 — Lajos Arpad, Jun 26 '13 at 07:21
@LajosArpad: Okay, so I guess Oracle doesn't support `WITH cte (columns...) AS`, so just omit the columns list. Just `WITH cte AS (inner query...)`, and make sure that both inner queries have their columns aliased (which, I think they do already). — voithos, Jun 26 '13 at 15:53

score 1 · Answer 2 · answered Jun 25 '13 at 22:00

1

As per in Oracle you can use listagg to achive this -

select t1.Person_Name, listagg(t2.Popularity_Value)
                        within group(order by t2.Popularity_Value) 
 from t1, t2
where t1.Person_Name = t2.Person_Name (+)
group by t1.Person_Name

I hope this will solve your problem.

But the comment you have given after @DavidJashi question .. well this is not sql standard and I think he is correct. I am also with David that you can not achieve this in pure sql statement.

answered Jun 25 '13 at 22:00

pratik garg

3,282
1
17
21

Thank you, that solves the problem, but it is not technology-agnostic, as you cannot use listagg in any SQL-implementer technology, it is not in the standards of SQL. If I solve the problem this way, then I cannot migrate my project to PostgreSQL, for instance. So this is not answering my question, but anyway, I thank you for trying. – Lajos Arpad Jun 25 '13 at 22:03
You are welcome and again I am telling that as by my knowledge there is no work around to do in pure SQL... But if you find any thing please update me also.. – pratik garg Jun 25 '13 at 22:07
Currently I have n+1 database requests instead of one, because a solution that works in only Oracle is not acceptable in this case. Voithos assured us he will give a solution, so I am waiting for that one. In the meantime I am thinking about that too, so maybe I will provide a solution. We will see. – Lajos Arpad Jun 25 '13 at 22:14
@LajosArpad: I don't know if I would consider what I posted as a "solution", unfortunately. – voithos Jun 25 '13 at 22:30

score 1 · Answer 3 · answered Mar 05 '21 at 20:07

I know that I'm SUPER late to the party, but for anyone else that might find this, I don't believe that this is possible using pure SQL92. As I discovered in the last few months fighting with NetSuite to try to figure out what Oracle methods I can and cannot use with their ODBC driver, I discovered that they only "support and guarantee" SQL92 standard.

I discovered this, because I had a need to perform a LISTAGG(). Once I found out I was restricted to SQL92, I did some digging through the historical records, and LISTAGG() and recursive queries (common table expressions) are NOT supported in SQL92, at all.

LISTAGG() was added in Oracle SQL version 11g Release 2 (2009 – 11 years ago: reference https://oracle-base.com/articles/misc/string-aggregation-techniques#listagg) , CTEs were added to Oracle SQL in version 9.2 (2007 – 13 years ago: reference https://www.databasestar.com/sql-cte-with/).

VERY frustrating that it's completely impossible to accomplish this kind of effect in pure SQL92, so I had to solve the problem in my C# code after I pulled a ton of extra unnecessary data. Very frustrating.

How to concatenate rows delimited with comma using standard SQL?

3 Answers3