当前位置: 动力学知识库 > 问答 > 编程问答 >

postgresql 9.3 - Interleave two SQL columns into paired array

问题描述:

I'm trying to Interleave two SQL columns into paired array.

How do I turn this:

id key val

--- --- ---

1 a apple

1 b boy

2 x xray

2 y yellow

into this:

new_id array

--- ---

1 {a,apple},{b,boy}

2 {x,xray},{y,yellow}

网友答案:

This solution is very specific to this case (i.e. 2 items per id) so it might not be sufficient but it does work in your case:

select 
   id
   , ARRAY[min(kv), max(kv)] 
from 
   (select 
    id
    , ARRAY[key, val] kv from fruit
   ) s 
group by 
  id 
order by 
  id;

┌────┬───────────────────────┐
│ id │         array         │
├────┼───────────────────────┤
│ 1  │ {{a,apple},{b,boy}}   │
│ 2  │ {{x,xray},{y,yellow}} │
└────┴───────────────────────┘
(2 rows)
分享给朋友:
您可能感兴趣的文章:
随机阅读: