CODEWITHSUNDEEP
google-bigquery
Gallory Knox
Gallory Knox

Reputation: 343

BigQuery Standrad SQL How to transpose a row into a column

I am trying to use the new Standard SQL feature in BigQuery. However, I cannot happen to find reference in the documentation as to how to do the following:

The input I have is:

enter image description here

I am trying to transpose it to the following:

enter image description here

I was hoping to use PIVOT functions as in SQL, but I cannot happen to find how creating PIVOTs works in the BigQuery Standard SQL feature.

Any ideas how to achieve this?

Upvotes: 2

Views: 4012

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

See the option below

Number of rows you have in YourTable doesn't matter here - but number of column Does!

You will need to maintain ARRAY creation under temp named subquery by having as many SELECTs as columns you have to transpose. In below example there are three as it is in your question.

There is no way to avoid the manual work for this part rather then by coding in client of your choice - by loading table schema with tables.get API and looping through schema's fields while building below query and then finally running it.
Again - this requires client coding, which doesn't look like what you are looking for here

So, here we go: 

WITH temp AS (
  SELECT B, 
    ARRAY(
      SELECT AS STRUCT 't1' AS B1, t1 AS Value UNION ALL 
      SELECT AS STRUCT 'm1' AS B1, m1 AS Value UNION ALL 
      SELECT AS STRUCT 'p1' AS B1, p1 AS Value
    ) AS bb  
  FROM YourTable 
)
SELECT B, bb.B1 AS B1, bb.Value AS Value 
FROM temp t, t.bb

You can test it with 

WITH YourTable AS (
  SELECT 'a' AS B,  1 AS t1,  2 AS m1,  3 AS p1 UNION ALL
  SELECT 'b' AS B,  4 AS t1,  5 AS m1,  6 AS p1 UNION ALL
  SELECT 'c' AS B,  7 AS t1,  8 AS m1,  9 AS p1 UNION ALL
  SELECT 'd' AS B, 10 AS t1, 11 AS m1, 12 AS p1 UNION ALL
  SELECT 'e' AS B, 13 AS t1, 14 AS m1, 15 AS p1 UNION ALL
  SELECT 'f' AS B, 16 AS t1, 17 AS m1, 18 AS p1 
),
temp AS (
  SELECT B, 
    ARRAY(
      SELECT AS STRUCT 't1' AS B1, t1 AS Value UNION ALL 
      SELECT AS STRUCT 'm1' AS B1, m1 AS Value UNION ALL 
      SELECT AS STRUCT 'p1' AS B1, p1 AS Value
    ) AS bb
  FROM YourTable 
)
SELECT B, bb.B1 AS B1, bb.Value AS Value 
FROM temp t, t.bb

Upvotes: 0

Pentium10
Pentium10

Reputation: 207828

This is not Pivoting, it's UNION (comma operation)

Use simple union for

select B\B1 as B, 't1' as B, t1 as value,
select B\B1 as B, 'm1' as B, m1 as value,
select B\B1 as B, 'p1' as B, p1 as value;

Upvotes: 1

Related Questions