PostgreSQL Query - How to do this?

0

How do I do a SQL query on 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 this possible?

    
asked by anonymous 24.02.2015 / 22:21

3 answers

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
    
25.02.2015 / 15:59
0

Dude, your ID idea is wrong, watch out, the ID must be unique ... You will not be able to do this just with Query in SQL, you'll 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;
}

With this you create an array ($ array) with the ID key and the result of Col1.

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

Need to be a query?

    
24.02.2015 / 22:39
0

If Col1 is sorted, as in the example, it looks like this for 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
    
25.02.2015 / 03:49