News for hackers. Stuff that matters.
Re:SQL Pivot: Converting Rows to Columns by danso on Thursday November 01, 2018 @01:15PM (#18356192)
I vaguely understand (on a theoretical level) why the syntax for pivoting in databases is necessarily convoluted, but it does underscore to me how spreadsheets have a great advantage for everyday data-exploring, at least when it comes to ease of pivoting.

(by "everyday", I mean working with datasets small enough to fit within a spreadsheet's limits, and aren't yet important enough to need the reliability/regularity that comes with importing it to a database)

Re:SQL Pivot: Converting Rows to Columns by nerdponx on Thursday November 01, 2018 @01:49PM (#18356533)
The ability to use Excel as a "front-end" for other data sources is underrated. For all its warts, Excel is a great data manipulation and exploration tool.
Re:SQL Pivot: Converting Rows to Columns by BariumBlue on Thursday November 01, 2018 @02:42PM (#18357063)
Due to system limitations, I made a stats tracker for my team in AccessDB. Due to the awfulness of access, I made an excel&vba front-end that would push data to and pull data from access.

It's been working surprisingly well so far, with no reported issues

Re:SQL Pivot: Converting Rows to Columns by bhandziuk on Thursday November 01, 2018 @04:33PM (#18358144)
I'm curious what you found to be the brickwall for you when using Access?
Re:SQL Pivot: Converting Rows to Columns by BariumBlue on Thursday November 01, 2018 @06:30PM (#18359047)
The existing "solution" was a gigantic excel where people would put it times and names.

In Access, when you enter times, you have to use a specific format, (the datetime group specified by the Windows default) rather than just "23:40" or w/e. I've seen a hack where the hour and minutes are separate boxes, but it wasn't pretty. Just this issue was enough to make Access unappealing.

Access's interface is a bit unintuitive, and given that most people I worked with weren't particularly technically capable, I preferred to present them with the familiar Excel interface (and a few extra buttons), rather than teach them an application they've never used before.

Plus, the existing workflow already used Excel, so I hijacked their workflow and their routine wouldn't be modified much at all (introducing a completely new system would cause a lot of friction and pushback, but one or two extra buttons was fine).

Re:SQL Pivot: Converting Rows to Columns by bhandziuk on Friday November 02, 2018 @10:39AM (#18363326)
Huh. Next time you can set the date format to "mm/dd/yyyy hh:nn" and you can enter 24 hour times with a date.

But I understand wanting to not disrupt an existing workflow too much.

Re:SQL Pivot: Converting Rows to Columns by karkisuni on Thursday November 01, 2018 @01:26PM (#18356286)
I wish there was something like this in postgres. tablefunc is a lot more complicated than this syntax.
Re:SQL Pivot: Converting Rows to Columns by netcraft on Thursday November 01, 2018 @02:44PM (#18357081)
There are a couple ways to pivot in postgres, here is one that uses json functionality https://gist.github.com/ryanguill/101a19fb6ae6dfb26a01396c53...
Re:SQL Pivot: Converting Rows to Columns by forkandwait on Thursday November 01, 2018 @01:52PM (#18356566)
I would like something with simple functionality, where I can just specify the table name, the category column, and the data column and get a new table transposed ibto columns. I would be happy to do the aggregation outside the pivot process. PROC TRANSPOSE in SAS would be a good model.
Re:SQL Pivot: Converting Rows to Columns by GorgeRonde on Friday November 02, 2018 @09:22AM (#18362794)
Dynamic row to column pivotation/transpose in Postgres made simple.

https://github.com/hnsl/colpivot

Re:SQL Pivot: Converting Rows to Columns by nevi-me on Thursday November 01, 2018 @12:46PM (#18355866)
I had a quick look at some online material on implementations of pivot, as well as what I consider my go-to reference [0] for Apache projects' SQL support (Apache Calcite).

Looks like each DB has a proprietary way of pivoting, and I couldn't find anything on pivot in Calcite. Does anyone know if the SQL impl of pivot was created specifically for Spark, or whether it'd be upstreamed to Calcite? The benefit would be that other projects that rely on Calcite for SQL support would also have pivot capabilities. I'm thinking of Apache Beam.

- [0] https://calcite.apache.org/docs/reference.html

Re:SQL Pivot: Converting Rows to Columns by reilly3000 on Thursday November 01, 2018 @12:56PM (#18355984)
This article enlightened me on the underlying logic, as well as some different implementations. https://modern-sql.com/use-case/pivot
Re:SQL Pivot: Converting Rows to Columns by nevi-me on Thursday November 01, 2018 @01:11PM (#18356154)
I was lazy to add its link, that's where I learnt about the proprietary implementations of various databases.

I wonder if we'll ever get an ANSI-2019 SQL version that tries to merge what various DB vendors have branched off and done. Maybe it already exists? https://www.whoishostingthis.com/resources/ansi-sql-standard...

Re:SQL Pivot: Converting Rows to Columns by rxin on Thursday November 01, 2018 @01:17PM (#18356201)
Apache Spark doesn't use Calcite.
Re:SQL Pivot: Converting Rows to Columns by IshKebab on Thursday November 01, 2018 @02:19PM (#18356843)
This is very nice. I've currently had to resort to MariaDB's dynamic columns for my purposes (much nicer than a traditional entity-attribute-value solution) but this looks even nicer. Is this a Spark-only thing?
Re:SQL Pivot: Converting Rows to Columns by 51Cards on Thursday November 01, 2018 @01:35PM (#18356389)
mySQL needs this as well. We do a lot of pivot style reports for clients and while we have it down to a bit of a science now the resulting SQL is a debugging mess and not terribly efficient. (though it still runs fairly quick)
Re:SQL Pivot: Converting Rows to Columns by m0dest on Thursday November 01, 2018 @02:47PM (#18357122)
I'd love to see this in Presto. It would make my life 1000x easier.
Re:SQL Pivot: Converting Rows to Columns by gildas on Thursday November 01, 2018 @01:08PM (#18356127)
Off topic: the GPDR banner on this site is a good example of dark pattern. Scrolling into a webpage does not mean I consent collecting personal info about me.
Re:SQL Pivot: Converting Rows to Columns by gdhbcc on Thursday November 01, 2018 @01:19PM (#18356220)
It's a good example of GDPR non-compliance too.
Re:SQL Pivot: Converting Rows to Columns by dspillett on Friday November 02, 2018 @06:04AM (#18361825)
I didn't even get a banner, so presumably I'm assumed to have consented without having taken any action.
Re:SQL Pivot: Converting Rows to Columns by ediardo on Thursday November 01, 2018 @02:34PM (#18356995)
I can't see the banner...
Re:SQL Pivot: Converting Rows to Columns by gildas on Thursday November 01, 2018 @06:28PM (#18359038)
Downvoting won't make it more compliant...

Edit: I thought we could discuss about dark patterns on HN. I was obviously wrong. Sorry for that...

Re:SQL Pivot: Converting Rows to Columns by dspillett on Friday November 02, 2018 @06:09AM (#18361845)
The downvotes on the original post are probably because the comment is being seen as off topic. Sometimes comments like that are accepted, sometimes not, I can't say I've noticed much of a patter there other than they are far less welcome on HN than, for example, Reddit.

I usually play safe and reserve side-topic comments to a post-script after something more immediately relevant (and if I don't have something to say on the thread topic, keep them to myself).

The downvotes on this one are going to be because of complaining about downvotes!