How to transpose from column to rows?

Question

I have a table:

a b c
<br> 1 2 3

a,b,c are integer. how can i transpose the rows to two columns:

<table style="width:100%">
  <tr>
    <td>col1</td>
    <td>col2</td> 
  
  </tr>
  <tr>
    <td>a</td>
    <td>1</td> 
    
  </tr>
   <tr>
    <td>b</td>
    <td>2</td> 
    
  </tr>
   <tr>
    <td>c</td>
    <td>3</td> 
    
  </tr>
</table>

(the number of columns in the first table is not set) thanks,

Possible duplicate of [How to simulate a pivot table with BigQuery?](http://stackoverflow.com/questions/19414609/how-to-simulate-a-pivot-table-with-bigquery) — Pentium10, May 24 '16 at 12:42

Mikhail Berlyant · Accepted Answer · 2016-05-24T15:25:04.540

below should work, but at least you need to know in advance maximum possible set of columns in your [first] table
Assuming max number of expected columns are 6 - respectivelly: a, b, c, d, e, f

SELECT
  id,
  REGEXP_EXTRACT(pair, r'(\w+):') AS key,
  REGEXP_EXTRACT(pair, r':(\w+)') AS value
FROM (
  SELECT
    id, 
    SPLIT(CONCAT(
      'a:',IFNULL(STRING(a), ''), 
      ',b:', IFNULL(STRING(b), ''), 
      ',c:', IFNULL(STRING(c), ''), 
      ',d:', IFNULL(STRING(d), ''), 
      ',e:', IFNULL(STRING(e), ''), 
      ',f:', IFNULL(STRING(f), '')
    )) AS pair
  FROM (
    SELECT * FROM 
    YourTable,
    (SELECT NULL AS a, NULL AS b, NULL AS c, NULL AS d, NULL AS e, NULL AS f)
  )
)
HAVING NOT value IS NULL

Try below as an example:

SELECT
  id,
  REGEXP_EXTRACT(pair, r'(\w+):') AS key,
  REGEXP_EXTRACT(pair, r':(\w+)') AS value
FROM (
  SELECT
    id, 
    SPLIT(CONCAT(
      'a:',IFNULL(STRING(a), ''), 
      ',b:', IFNULL(STRING(b), ''), 
      ',c:', IFNULL(STRING(c), ''), 
      ',d:', IFNULL(STRING(d), ''), 
      ',e:', IFNULL(STRING(e), ''), 
      ',f:', IFNULL(STRING(f), '')
    )) AS pair
  FROM (
    SELECT * FROM 
    (SELECT 1 AS id, 1 AS a, 2 AS b, 3 AS c),
    (SELECT 2 AS id, 4 AS a, 5 AS b, 6 AS c, 7 AS d),
    (SELECT 3 AS id, 8 AS a, 9 AS b, 10 AS c, 11 AS e),
    (SELECT NULL AS id, NULL AS a, NULL AS b, NULL AS c, NULL AS d, NULL AS e, NULL AS f)
  )
)
HAVING NOT value IS NULL

Added for: i'm looking for a solution without union or knowing the max number of expected columns

using client side code - you can
a) get schema of the table Tables: get API
b) build dynamic sql like in above answer (w/o union now)
c) execute sql

Thanks for you answer, but i'm looking for a solution without union or knowing the max number of expected columns. — Adi Cohen, May 24 '16 at 14:48

How to transpose from column to rows?

1 Answers1