pivot, transpose rows in coumns

[GoogleCodeExporter]

I have this query :

SELECT
  CONCAT(STRING ( year ),'-', CASE WHEN LENGTH(STRING (month))=1 THEN CONCAT( '0','', STRING (month)) ELSE STRING (month) END) AS ano_x_m,
  month,
  year,
  AVG( mean_temp )
FROM
  [publicdata:samples.gsod]
GROUP BY
  ano_x_m,
  month,
  year
ORDER BY
  year DESC,
  month DESC

I'd like to transpose the values of "ano_x_m" in columns, as I can do with a 
pivot in MsSQL.

pivto example in Mssql:
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

I have attached a screenshot with the expected result

Original issue reported on code.google.com by JuanFh...@gmail.com on 29 Dec 2015 at 7:47

Attachments:

• Captura.JPG

[GoogleCodeExporter]

The best advice for pivot simulation in BigQuery can be found on Stack Overflow:
http://stackoverflow.com/questions/26272514/how-to-pivot-table-in-big-query

To summarize it, BigQuery will not support a direct pivot in its current query 
version, but many uses can be simulated through NEST() + INDEX().

Original comment by wes...@google.com on 6 Jan 2016 at 10:23

• Changed state: WontFix

[GoogleCodeExporter]

Hi,

thanks for the answer, ofcourse is a way to do pivot, but in my case the 
problem is that my columns are dynamic, and change every month. are there a way 
to do  it dynamic?

Original comment by JuanFh...@gmail.com on 8 Jan 2016 at 4:05

[GoogleCodeExporter]

Unfortunately no. If you don't know the column names ahead of time, the best 
you can do is to write some client-side code against the BQ API to fetch the 
table schema and build a query according to one of the Stack Overflow 
suggestions linked above.

Original comment by jcon...@google.com on 8 Jan 2016 at 5:24

[GoogleCodeExporter]

oks, I hope soon will be enable pivot in BQ. Thanks for all!

Original comment by JuanFh...@gmail.com on 8 Jan 2016 at 6:00