SQL如何将行追加到列？

I have a table with many rows and two columns. So for instance, my table would look like this:

我有一个包含许多行和两列的表。所以例如,我的表看起来像这样:

ID   Name

1     Bob

2    Jerry

3    Tom

4    John

Ultimately what I want though is for the table to look like this:

最终我想要的是桌子看起来像这样:

ID Name ID Name ID Name ID Name

1  Bob   2 Jerry 3 Tom 4 John

How would I do this in SQL?

我将如何在SQL中执行此操作?

Thanks

1 个解决方案

#1

You can use LISTAGG to aggregate your rows in one column but like I said, it will be only one column. BTW, as I know, you can't have multiple columns with the same name :

您可以使用LISTAGG在一列中聚合行,但就像我说的那样,它只是一列。顺便说一下,据我所知,你不能有多个具有相同名称的列:

SQL Fiddle

Oracle 11g R2 Schema Setup:

Oracle 11g R2架构设置:

CREATE TABLE Table1
    ("ID" int, "Name" varchar2(5))
;

INSERT ALL 
    INTO Table1 ("ID", "Name")
         VALUES (1, 'Bob')
    INTO Table1 ("ID", "Name")
         VALUES (2, 'Jerry')
    INTO Table1 ("ID", "Name")
         VALUES (3, 'Tom')
    INTO Table1 ("ID", "Name")
         VALUES (4, 'John')
SELECT * FROM dual
;

Query 1:

SELECT listagg ("ID" || ' ' || "Name", ' ') WITHIN GROUP (ORDER BY ID) Result
FROM Table1

Results:

|                     RESULT |
|----------------------------|
| 1 Bob 2 Jerry 3 Tom 4 John |

#1