Big Query Gap Fill in group

Question

I am trying to get all of table A to repeat for every customer in table B so far my process is as follows:

Table A:

  PStage  StageOrder
  pros   1
  dis    2
  qual   3
  val    4
  prop   5
  neg    6
  stal   7
  won    8

Table B:

 ID  stage   
 A   Dis      
 A   Won

From there I more or less get what I want by

Select A.* , B.* left join on a.pstage = b.Stage

so I get

PStage  StageOrder  ID    stage
  pros      1        null  null
  dis       2         A    dis
  qual      3        null  null
  val       4        null  null
  prop      5        null  null
  neg       6        null  null
  stal      7        null  null
  won       8         A    won

But when Table B is

 ID  stage   
 A   dis      
 A   won  
 B   dis 
 B   neg

I get

PStage  StageOrder  ID    stage
  pros      1        null  null
  dis       2         A    dis
  dis       2         B    dis
  qual      3        null  null
  val       4        null  null
  prop      5        null  null
  neg       6        B     neg
  stal      7        null  null
  won       8         A    won

What I want to have happen is

PStage  StageOrder  ID    stage
  pros      1        null  null
  dis       2         A    dis
  qual      3        null  null
  val       4        null  null
  prop      5        null  null
  neg       6        null  null
  stal      7        null  null
  won       8         A    won
  pros      1        null  null
  dis       2         B    dis
  qual      3        null  null
  val       4        null  null
  prop      5        null  null
  neg       6        B     neg
  stal      7        null  null
  won       8        null  null

I am fine with ID repeating in all null spaces if that makes it work as well

any help is much appreciated

user_5221944 · Answer

Consider below approach

select a.*, c.*
from tableA a, (select distinct id from tableB) b
left join tableB c 
on b.id = c.id 
and PStage = stage
order by b.id, StageOrder

if applied to sample data in your question - output is

Big Query Gap Fill in group

1 Answers