BigQuery - Sql Pivot Converting Rows To Columns [118843104]

Fixed

Feature Request

Status Update

No update yet.

Description

ku...@xiatech.co.uk

created issue #1

Nov 2, 2018 02:35PM

Hi,

I would like a feature that allows converting rows into columns in a pivot like style. Interestingly, spark SQL in the next release is doing something similar which looks super nice.

https://databricks.com/blog/2018/11/01/sql-pivot-converting-rows-to-columns.html

Such a capability in native BQ SQL would be super cool and useful. Im su

What you would like to accomplish:

- Pivoting data is very common, and excel is of course super easy. Ive needed to do similar with representations of financial data (stock / sales) for current year and last year for example, grouped by say season....such need is often for exec level dashboards

How this might work:

- Very similar to the spark model in the recent blog post! The examples show an intuitive use for this.

If applicable, reasons why alternative solutions are not sufficient:

- Im sure some SQL boffins could achieve similar but likely with some complex SQL (Mikhail or Elliott could do no doubt)?? Would be nice to have it as concise part of the BQ SQL language.

Comments

bl...@google.com <bl...@google.com> Nov 2, 2018 02:37PM

Assigned to lk...@google.com.

lk...@google.com <lk...@google.com> Nov 2, 2018 04:18PM

Reassigned to me...@google.com.

me...@google.com <me...@google.com> Nov 2, 2018 05:58PM

Reassigned to el...@google.com.

ce...@gmail.com <ce...@gmail.com> #2Apr 6, 2019 01:52PM

is there any news on this?

el...@google.com <el...@google.com> #3Apr 8, 2019 04:33PM

You can pivot already, e.g. using a conditional aggregation:

https://stackoverflow.com/questions/50796224/pivot-rows-in-big-query. Client tools such as Sheets, Excel, Data Studio, etc. are almost always in a better position to pivot the data returned by BigQuery since if you want to do it in a single BigQuery query, you need to provide the list of values and column names to pivot.

ce...@gmail.com <ce...@gmail.com> #4Apr 8, 2019 04:47PM

I had to implement a solution to pivot 300 rows into columns. We need a better way to pivot rows. Conditional aggregation or sql generators are cumbersome and error-prone.

el...@google.com <el...@google.com> #5Apr 8, 2019 04:55PM

Even Kurt's example linked above still requires you to write out 300 clauses for each column. Generating SQL really is the best option.

pe...@gmail.com <pe...@gmail.com> #6Sep 30, 2019 04:48PM

We want something like:

SELECT * FROM
(
SELECT
category_name,
product_id
FROM
production.products p
INNER JOIN production.categories c
ON c.category_id = p.category_id
) t
PIVOT(
COUNT(product_id)
FOR category_name IN (
[Children Bicycles],
[Comfort Bicycles],
[Cruisers Bicycles],
[Cyclocross Bicycles],
[Electric Bikes],
[Mountain Bikes],
[Road Bikes])
) AS pivot_table;

el...@google.com <el...@google.com> Nov 8, 2019 04:45PM

Reassigned to th...@google.com.

th...@google.com <th...@google.com> Nov 18, 2019 06:29PM

Reassigned to ja...@google.com.

ce...@gmail.com <ce...@gmail.com> #7Nov 18, 2019 06:33PM

Is there a progress on this issue? :)

pe...@gmail.com <pe...@gmail.com> #8Jun 11, 2020 08:53AM

Any update regarding PIVOT functionality?

ho...@google.com <ho...@google.com> #9Jun 24, 2020 05:07PM

Check this solution in the meantime:

-

https://medium.com/@hoffa/easy-pivot-in-bigquery-one-step-5a1f13c6c710

[Deleted User] <[Deleted User]> #10Jun 25, 2020 01:57AM

update!

[Deleted User] <[Deleted User]> #11Jul 7, 2020 09:46PM

not sure where to upvote so writing here instead. i would love this!

ry...@energydrive.co.za <ry...@energydrive.co.za> #12Jul 18, 2020 07:31PM

Yes please!

an...@storrs.ca <an...@storrs.ca> #13Jul 22, 2020 08:21PM

Yes, great idea!

de...@iwc.com <de...@iwc.com> #14Jul 28, 2020 02:55PM

Would be very helpful indeed.

[Deleted User] <[Deleted User]> #15Aug 11, 2020 11:16AM

Yes, great idea!

al...@gmail.com <al...@gmail.com> #16Aug 15, 2020 11:42AM

Would like to use that too

bc...@gmail.com <bc...@gmail.com> #17Aug 18, 2020 05:47AM

[Deleted User] <[Deleted User]> #18Aug 18, 2020 12:22PM

mi...@gmail.com <mi...@gmail.com> #19Aug 18, 2020 01:21PM

[Deleted User] <[Deleted User]> #20Aug 20, 2020 09:34PM

[Deleted User] <[Deleted User]> #21Aug 21, 2020 04:06AM

mi...@gmail.com <mi...@gmail.com> #22Aug 21, 2020 08:37AM

[Deleted User] <[Deleted User]> #23Aug 26, 2020 05:57AM

[Deleted User] <[Deleted User]> #24Sep 4, 2020 10:11AM

va...@gmail.com <va...@gmail.com> #25Sep 5, 2020 04:33AM

bw...@google.com <bw...@google.com> Sep 13, 2020 07:00PM

Accepted by bw...@google.com.

no...@google.com <no...@google.com> #26Sep 15, 2020 01:34PM

ad...@sablecard.com <ad...@sablecard.com> #27Sep 16, 2020 08:08PM

an...@ngpcap.com <an...@ngpcap.com> #28Sep 21, 2020 12:05PM

bw...@google.com <bw...@google.com> Sep 22, 2020 07:05AM

Accepted by ja...@google.com.

mi...@gmail.com <mi...@gmail.com> #29Sep 29, 2020 02:58PM

[Deleted User] <[Deleted User]> #30Oct 5, 2020 04:29PM

be...@cherre.com <be...@cherre.com> #31Oct 7, 2020 09:49PM

[Deleted User] <[Deleted User]> #32Oct 14, 2020 09:50AM

al...@pearson.com <al...@pearson.com> #33Oct 20, 2020 10:31PM

en...@gmail.com <en...@gmail.com> #34Oct 21, 2020 02:55AM

jo...@hotmail.co.uk <jo...@hotmail.co.uk> #35Oct 21, 2020 12:28PM

am...@gmail.com <am...@gmail.com> #36Oct 23, 2020 12:03PM

ar...@gmail.com <ar...@gmail.com> #37Oct 25, 2020 08:01PM

ad...@gmail.com <ad...@gmail.com> #38Oct 27, 2020 08:47PM

jc...@gmail.com <jc...@gmail.com> #39Oct 28, 2020 02:05PM

[Deleted User] <[Deleted User]> #40Oct 28, 2020 10:07PM

lu...@gmail.com <lu...@gmail.com> #41Oct 28, 2020 11:10PM

ka...@gmail.com <ka...@gmail.com> #42Nov 3, 2020 12:00PM

[Deleted User] <[Deleted User]> #43Nov 3, 2020 01:30PM

[Deleted User] <[Deleted User]> #44Nov 4, 2020 02:04PM

[Deleted User] <[Deleted User]> #45Nov 5, 2020 03:31PM

ch...@bstock.com <ch...@bstock.com> #46Nov 7, 2020 03:51AM

pp...@cludo.pl <pp...@cludo.pl> #47Nov 9, 2020 11:04PM

ao...@googlemail.com <ao...@googlemail.com> #48Nov 10, 2020 09:09PM

ar...@biocogniv.com <ar...@biocogniv.com> #49Nov 13, 2020 01:44AM

Yes to this please! We've been using pivots everywhere, alas the queries to build them are cumbersome and slow.

+1 for this feature – esp if it's optimized!

ma...@playtomic.io <ma...@playtomic.io> #50Nov 17, 2020 01:50PM

jo...@worklytics.co <jo...@worklytics.co> #51Nov 19, 2020 11:26PM

[Deleted User] <[Deleted User]> #52Nov 20, 2020 01:25AM

gv...@google.com <gv...@google.com> #53Nov 27, 2020 07:43AM

mw...@claimsolution.com <mw...@claimsolution.com> #54Nov 30, 2020 03:09PM

mb...@stylepit.com <mb...@stylepit.com> #55Dec 2, 2020 12:49PM

[Deleted User] <[Deleted User]> #56Dec 3, 2020 10:13PM

an...@optus.com.au <an...@optus.com.au> #57Dec 4, 2020 01:07AM

el...@waitrose.co.uk <el...@waitrose.co.uk> #58Dec 4, 2020 03:16PM

[Deleted User] <[Deleted User]> #59Dec 8, 2020 11:38AM

be...@gmail.com <be...@gmail.com> #60Dec 9, 2020 02:11PM

[Deleted User] <[Deleted User]> #61Dec 11, 2020 10:07PM

we would love to have this. +1 +1

ch...@gmail.com <ch...@gmail.com> #62Dec 14, 2020 08:12PM

wk...@gmail.com <wk...@gmail.com> #63Dec 15, 2020 10:55PM

ni...@gmail.com <ni...@gmail.com> #64Dec 16, 2020 04:03AM

ay...@gmail.com <ay...@gmail.com> #65Dec 17, 2020 12:31PM

tu...@bouvet.no <tu...@bouvet.no> #66Dec 19, 2020 06:55PM

ru...@ringside.ai <ru...@ringside.ai> #67Dec 21, 2020 06:50PM

aa...@schnucks.com <aa...@schnucks.com> #68Dec 22, 2020 12:08PM

da...@darrenhe.com <da...@darrenhe.com> #69Dec 25, 2020 07:23AM

Please implement!

co...@thenextcloset.com <co...@thenextcloset.com> #70Jan 7, 2021 01:32PM

ma...@gmail.com <ma...@gmail.com> #71Jan 7, 2021 05:56PM

fl...@gmail.com <fl...@gmail.com> #72Jan 15, 2021 10:37PM

ne...@ae.com <ne...@ae.com> #73Jan 16, 2021 01:39PM

ka...@gmail.com <ka...@gmail.com> #74Jan 17, 2021 08:36AM

da...@emerysapp.com <da...@emerysapp.com> #75Jan 18, 2021 05:15PM

ml...@gmail.com <ml...@gmail.com> #76Jan 18, 2021 09:13PM

ar...@loblaw.ca <ar...@loblaw.ca> #77Jan 18, 2021 09:13PM

da...@aller.com <da...@aller.com> #78Jan 19, 2021 08:54AM

gr...@platform.sh <gr...@platform.sh> #79Jan 21, 2021 12:19PM

yo...@gmail.com <yo...@gmail.com> #80Jan 24, 2021 09:01PM

[Deleted User] <[Deleted User]> #81Jan 27, 2021 12:12PM

iz...@gmail.com <iz...@gmail.com> #82Jan 29, 2021 02:38PM

[Deleted User] <[Deleted User]> #83Feb 2, 2021 03:55PM

[Deleted User] <[Deleted User]> #84Feb 3, 2021 07:27PM

[Deleted User] <[Deleted User]> #85Feb 4, 2021 01:05AM

nm...@falabella.cl <nm...@falabella.cl> #86Feb 4, 2021 03:30PM

tb...@gmail.com <tb...@gmail.com> #87Feb 10, 2021 09:24PM

[Deleted User] <[Deleted User]> #88Feb 11, 2021 03:25AM

lu...@loblaw.ca <lu...@loblaw.ca> #89Feb 17, 2021 07:23AM

tu...@gmail.com <tu...@gmail.com> #90Feb 18, 2021 07:36PM

jj...@hl.agency <jj...@hl.agency> #91Feb 21, 2021 10:11PM

du...@digital.cabinet-office.gov.uk <du...@digital.cabinet-office.gov.uk> #92Feb 22, 2021 10:01AM

aj...@gmail.com <aj...@gmail.com> #93Feb 22, 2021 09:52PM

ar...@smilingmind.com.au <ar...@smilingmind.com.au> #94Mar 1, 2021 06:11AM

[Deleted User] <[Deleted User]> #95Mar 1, 2021 09:18AM

[Deleted User] <[Deleted User]> #96Mar 2, 2021 04:35AM

ol...@blablacar.com <ol...@blablacar.com> #97Mar 8, 2021 11:25AM

ia...@pendo.io <ia...@pendo.io> #98Mar 15, 2021 01:06PM

[Deleted User] <[Deleted User]> #99Mar 16, 2021 01:35AM

[Deleted User] <[Deleted User]> #100Mar 17, 2021 04:32PM

ma...@theloyalist.com <ma...@theloyalist.com> #101Mar 22, 2021 01:16PM

br...@gmail.com <br...@gmail.com> #102Mar 25, 2021 01:09PM

na...@apmex.com <na...@apmex.com> #103Mar 30, 2021 12:46PM

ia...@kintosllc.com <ia...@kintosllc.com> #104Apr 6, 2021 12:55AM

da...@tinuiti.com <da...@tinuiti.com> #105Apr 7, 2021 10:55PM

pa...@gmail.com <pa...@gmail.com> #106Apr 10, 2021 12:15AM

ku...@googlemail.com <ku...@googlemail.com> #107Apr 12, 2021 06:47AM

ge...@gmail.com <ge...@gmail.com> #108Apr 12, 2021 07:59PM

mi...@pwc.com <mi...@pwc.com> #109Apr 14, 2021 02:24AM

at...@wayfair.com <at...@wayfair.com> #110Apr 14, 2021 11:56AM

cv...@gmail.com <cv...@gmail.com> #111Apr 15, 2021 10:05AM

[Deleted User] <[Deleted User]> #112Apr 20, 2021 01:28PM

er...@gmail.com <er...@gmail.com> #113Apr 20, 2021 01:41PM

va...@ingka.ikea.com <va...@ingka.ikea.com> #114Apr 22, 2021 12:37PM

kh...@justeattakeaway.com <kh...@justeattakeaway.com> #115Apr 22, 2021 04:09PM

ch...@gmail.com <ch...@gmail.com> #116Apr 27, 2021 05:48PM

ni...@gmail.com <ni...@gmail.com> #117Apr 28, 2021 09:29AM

dl...@woolworths.com.au <dl...@woolworths.com.au> #118May 2, 2021 05:00AM

an...@gmail.com <an...@gmail.com> #119May 3, 2021 02:17PM

ge...@gmail.com <ge...@gmail.com> #120May 4, 2021 10:47PM

bw...@google.com <bw...@google.com> #121May 6, 2021 03:50AM

Thanks everyone for the suggestions. We are working hard on this one.

ke...@autopediacar.com <ke...@autopediacar.com> #122May 10, 2021 08:37AM

vl...@google.com <vl...@google.com> #123May 10, 2021 09:36PM

Status: Assigned (reopened)

At long last: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator

[Deleted User] <[Deleted User]> #124May 11, 2021 10:11AM

ma...@retool.com <ma...@retool.com> #125May 14, 2021 09:24PM

ja...@google.com <ja...@google.com> #126May 25, 2021 12:02AM

Marked as fixed.

Launched on

https://cloud.google.com/bigquery/docs/release-notes#May_10_2021