Calculating median of 3 columns in a BigQuery table

Question

I am trying to build a query to calculate median of 3 column values. My table looks like below,

Item	Column 1	Column 2	Column 3
A	10	12	4
B	5	14	20
C	15	5	4

I want to be able to output,

Item	Column 1	Column 2	Column 3	Median
A	10	12	4	10
B	5	14	20	14
C	15	5	4	5

I have tried percentile_cont() but that seems to be only for values in a single column. How do i achieve this?

Answer 1

Consider below approach

select *, 
  ( select distinct percentile_disc(col, 0.5) over() 
    from unnest([Column1, Column2, Column3]) as col
  ) AS Median
from your_table       

if applied to sample data in your question - output is