Postgresql - Rows to Columns

Question

I have a table that has records as follows, 2 columns, col1id and col1:

AA1       TextAA1
AA2       TextAA2
AA3       TextAA3
AB1       TextAB1
AC1       TextAC1
BA1       TextBA1
BA2       TextBA2
BA3       TextBA3
BB1       TextBB1
BC1       TextBC1
CA1       TextCA1
CA2       TextCA2
CA3       TextCA3
CB1       TextCB1
CC1       TextCC1

I need to make a table that is like this for output:

TextAA1, TextAA2, TextAA3
TextBA1, TextBA2, TextBA3
TextCA1, TextCA2, TextCA3

There is nothing to tie the records together except the fact that they are grouped as AA1-3, BA1-3 etc. The other records ?B? and ?C? can be ignored.

I've looked at various answers including crosstab (installed) and tried to make a query using joins to no avail.

Here is one sample of what I've tried:

SELECT col1 as a1, row_number() OVER(ORDER BY col1id ASC) as a2
  FROM table01
  WHERE col1id  LIKE '_A_'
JOIN (
SELECT col2 as c1, row_number() OVER(ORDER BY col1id ASC) as c2
  FROM table01
  WHERE col1id LIKE '_A_') ON a2 = c2

But I can't get over a JOIN error.

How can i do this? Thanks in advance,

klin · Accepted Answer · 2018-10-30 12:30:39Z

3

Use array_agg() to aggregate col1 values in groups by first letter of col1id:

select arr[1] as a1, arr[2] as a2, arr[3] as a3
from (
    select array_agg(col1 order by col1id) as arr
    from table01
    where col1id like '_A_'
    group by left(col1id, 1) -- first letter of col1id
    order by 1
    ) sub

edited Oct 30, 2018 at 12:30

answered Jul 28, 2015 at 18:52

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Over a year ago

Absolutely nailed it, would never have thought of this, many thanks

Collectives™ on Stack Overflow

Postgresql - Rows to Columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related