CODEWITHSUNDEEP
sqlpostgresql
Alejandro Camba
Alejandro Camba

Reputation: 988

Multiple rows out of a single record

lets say that e.g if i did SELECT * FROM X i'd get

A1, A2, A3, A4, A5, A6, A7

is it possible to do a query that for each record returns 

A1, A2, A3    
A1, A2, A4
A1, A2, A5
A1, A2, A6
A1, A2, A7

????

My actual problem ( and this is just additional information, not required to answer the question ) --> i have a db about football matches so i have players etc but the table matches are like this: 

homeplayer1 (fk)
homeplayer2 (fk)
.
.
.
homeplayerN (fk)

and same for away players but i'm working on a multidimiensional db implementation that requires to have only one player associated per match so it'd be 22 records per match to fit the 22 players.

I'm using postgresql.

Upvotes: 0

Views: 50

Answers (2)

Mike
Mike

Reputation: 2005

 select a1, a2,
        unnest(array[a3,a4,a5,a6,a7])
   from X

Test on sqlfiddle.com

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Let's assume that some column defines the ordering; I'll call it ord:

with t as (
      select x.*, row_number() over (order by ord) as seqnum
      from x
     )
select t1.col, t2.col, t3.col
from t t1 join
     t t2
     on t1.seqnum = 1 and t2.seqnum = 2 join
     t t3
     on t3.seqnum > 2;

You may want to ask another question about your actual problem, which looks a bit different from what you asked in this question.

Upvotes: 1

Related Questions