CODEWITHSUNDEEP
sqlpostgresql
Barry
Barry

Reputation: 69

How to group by table and create the new columns to put the original value in there?

I have a table in postgresql

type id n   occurred_at
A   159 4   2013/12/20 18:05
A   159 5   2013/12/27 18:05
A   159 6   2014/1/20 18:05
A   159 8   2014/3/22 12:34
B   180 5   2014/3/29 12:34
B   180 6   2014/4/22 12:34
C   207 4   2014/3/13 03:24
C   207 8   2014/3/20 03:24
C   207 6   2014/4/13 03:24
D   157 4   2013/12/20 18:07
D   157 5   2013/12/27 18:07
D   157 6   2014/1/20 18:07
D   157 8   2013/1/20 17:41
D   157 8   2013/12/27 17:41
D   157 8   2014/1/20 17:41

I want different n which has same type and id in one row and order by occurred_at (The n is only for 6 or 8). The result just like below, I was trying to use group by type, id to get this but seems difficult. Does any guy have better ideas to do this? 

A   159  6 8
B   180  6
C   207  8 6
D   157  8 8 6 8

Upvotes: 0

Views: 45

Answers (2)

Hambone
Hambone

Reputation: 16397

I'm not 100% sure on what you want for the desired output, but if you are looking for a unique "n" value for each row based on the type and id, I think the row_number() analytic function would do that.

select
  type, id,
  row_number() over (partition by type, id order by occurred_at) as n,
  occurred_at
from my_table

Upvotes: 0

user330315
user330315

Reputation:

If you want a space separated list of n-values, you can use string_agg to aggregate all values:

select type, id, string_agg(n::text, ' ' order by occurred_at) as n_values
from the_table
group by type, id;

Upvotes: 1

Related Questions