0

Let's say I have a table like this

Col1  Col2

A     0.1

B     2.4

B     2.1

C     5.0

...   ...

and that I want to obtain this other table

ColA  ColB  ColC

0.1   0     0

0     2.4   0

0     2.1   0

0     0     5.0

...   ...   ...

To create one single column I can use a CASE WHEN as

SELECT CASE WHEN Col1 = 'A' THEN Col2 ELSE 0 END AS ColA FROM TABLE

But I don't know a priori how many different categories I have in Col1 and there can be many of them. How could I obtain this result with BigQuery?

amarchin
  • 2,044
  • 1
  • 16
  • 32
  • You are looking to 'PIVOT' the data so that you create columns out of rows – Martin Feb 01 '19 at 15:09
  • This is nothing you do with SQL. A query returns before-known columns, but you don't know them before. Two options: 1) Select single values and loop through your results with whatever programming language you are using (PHP, C#, ...). 2) Select all categories. Then build a query with one column per category. Run this query. – Thorsten Kettner Feb 01 '19 at 15:17
  • I need to do this with SQL not with another language. – amarchin Feb 01 '19 at 15:19

0 Answers0