Postgresql Query - How to do this?

Question

How to make an SQL query in this table:

ID Col2 Col1  
-- ---- ----
11   1    A
12   2    D
13   3    G
14   1    B
15   2    E
16   3    H
17   1    C
18   2    F
19   3    I

To return this result:

1   A   B   C
2   D   E   F
3   G   H   I

Is that even possible?

Answer 1

with t (id, c2, c1) as (
    values
        (11, 1, 'A'),
        (12, 2, 'D'),
        (13, 3, 'G'),
        (14, 1, 'B'),
        (15, 2, 'E'),
        (16, 3, 'H'),
        (17, 1, 'C'),
        (18, 2, 'F'),
        (19, 3, 'I')
)
select i, c1[1] as c1, c1[2] as c2, c1[3] as c3
from (
    select c2 as i, array_agg(c1) as c1
    from t
    group by c2
) s
order by 1
;
 i | c1 | c2 | c3 
---+----+----+----
 1 | A  | B  | C
 2 | D  | E  | F
 3 | G  | H  | I

Answer 2

Dude, your ID idea is wrong, careful with that, the ID should be unique... You will not be able to do this only with Query in SQL, you will have to do this with your main language...

In PHP I would do so:

$resultado = (sua query com a consulta);

foreach($resultado as $key => $value){
    $array[$value->id][] = $value->Col1;
}

This creates an array ($array) with the ID key and the Col1 result.

Maybe you can do this with SQL Server / Oracle with some more elaborate query...

Needs to be query?

Answer 3

If Col1 is ordered, as in the example, so is SQL Server:

CREATE TABLE #tmp 
( 
    ID INT, 
    Col2 INT,
    Col1 Varchar(1)
)

insert into #tmp (ID, Col2, Col1)
Values
    (11,   1,    'A'),
    (12,   2,    'D'),
    (13,   3,    'G'),
    (14,   1,    'B'),
    (15,   2,    'E'),
    (16,   3,    'H'),
    (17,   1,    'C'),
    (18,   2,    'F'),
    (19,   3,    'I')


select distinct tmp1.Col2, 
                tmp1.Col1, 
                tmp2.Col1,
                tmp3.Col1 
    from #tmp tmp1
        inner join #tmp tmp2
            on  tmp1.Col2 = tmp2.Col2 
            and tmp1.Col1 < tmp2.Col1
        inner join #tmp tmp3
            on  tmp1.Col2 = tmp3.Col2 
            and tmp1.Col1 < tmp3.Col1 
            and tmp2.Col1 < tmp3.Col1
   order by tmp1.Col2