-
5 ideas for new SQL features!
Last week I wrote about some of the newer innovations in the SQL space. This got me thinking. What other language features would be useful in a SQL environment? This post explores some ideas I’ve had around this. It’s purely speculative, but perhaps it will spark some ideas for you. 1. Optional GROUP BY clause In most GROUP BY queries, every non-aggregate must be listed in the GROUP BY clause. Sure, we have options, like GROUP BY 1, 2, 3 or GROUP BY ALL, but while these are convenient, they’re also busywork for most queries. So my first suggestion is…
-
Format A Row Of Data For SQL INSERT
The gist for this lambda function can be found here. If you sometimes need to quickly put some Excel data into a SQL table or use the data in a CTE, you may have found yourself doing something like this: Here’s a LAMBDA I’ve called SQLVALUES: =LAMBDA(t,LET(d,IFS(ISTEXT(t),”‘”&SUBSTITUTE(t,”‘”,”””)&”‘”,ISBLANK(t),”NULL”,LEFT(MAP(t,LAMBDA(x,CELL(“format”,x))),1)=”D”,TEXT(t,”‘YYYY-MM-DD HH:mm:ss’”),TRUE,t),”(“&TEXTJOIN(“,”,FALSE,d)&”)”)) This will: Wrap the tuple in parentheses Wrap text and dates in single-quotes Replace embedded single-quotes with escaped single-quotes Separate the columns with commas Format date-formatted cells as YYYY-MM-DD HH:mm:ss If we’re inserting multiple values and our SQL database supports a list of tuples, we can also do this: =LET(arr,A2:C6,BYROW(arr,SQLVALUES)&IF(LASTROW(arr),”;”,”,”)) Which is…