I’m aware that many of my readers of this site likely already know how to write sql to generate sql, or sql generators, but it’s not really much of a data blog if I don’t mention it. It’s also important to mention the time value this trick represents, with a few minutes of time, you can generate thousands of lines of code that would otherwise take hours to write. Let’s chat about one of the first things I learned about SQL which, no doubt, blew my mind:
Generate SQL with SQL.
Or to put another way, generating SQL statements via a single SQL command. Really cool stuff if you haven’t seen this before, keep reading!
Let’s say you wanted to find the number of badges for each type of badge in a table of badges. This is quite easy with a simple query, but let’s see how we can do it by writing SQL with SQL:
|
0 1 2 3 4 5 6 |
SELECT 'SELECT CAST(''' + BADGES.NAME + '''AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = ''' + BADGES.NAME + ''' UNION ALL' FROM BADGES GROUP BY BADGES.NAME; |
This example can be seen in the wild here for which that output can be observed as follows:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT CAST('64bit'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = '64bit' UNION ALL SELECT CAST('adfs2.0'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'adfs2.0' UNION ALL SELECT CAST('alamofire'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'alamofire' UNION ALL SELECT CAST('alfresco-share'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'alfresco-share' UNION ALL SELECT CAST('algolia'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'algolia' UNION ALL SELECT CAST('alsa'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'alsa' UNION ALL SELECT CAST('amazon-mws'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'amazon-mws' UNION ALL SELECT CAST('android'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'android' UNION ALL SELECT CAST('android-4.4-kitkat'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'android-4.4-kitkat' UNION ALL SELECT CAST('android-adapter'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'android-adapter' UNION ALL SELECT CAST('android-canvas'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'android-canvas' UNION ALL SELECT CAST('android-fragmentactivity'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'android-fragmentactivity' UNION ALL SELECT CAST('android-intent'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'android-intent' UNION ALL SELECT CAST('android-listfragment'AS VARCHAR(50)), COUNT(1) FROM BADGES WHERE BADGES.NAME = 'android-listfragment' --... --UNION ALL; |
You’ll see that the output of the first query provides the input of the second query, which ultimately gives the result we’re looking for.
While this is a simple task to solve by itself with an aggregate, this is the best example I can come up using the query editor at stack exchange. I assure you this is a trick that I use to save countless minutes, hours, days of time… A really great example is using the system tables of your DBMS. One that I use quite often is the ‘columns’ system table which allows you to generate some really creative SQL that is a huge timesaver. Here’s a real-world Teradata example:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT 'SELECT CAST(''' || TRIM(COLUMNNAME) || ''' AS VARCHAR(30)) AS COL_NAME, MAX(LENGTH(' || TRIM(COLUMNNAME) || ')) AS MAX_LENGTH FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ' GROUP BY ' || TRIM(COLUMNNAME) || ' UNION ALL' FROM DBC.COLUMNS WHERE TABLENAME = 'DB.FOO' AND DATABASENAME = 'DB'; /* Example Output */ SELECT 'COLUMN_A' AS COL_NAME, MAX(LENGTH(COLUMN_A)) AS MAX_LENGTH FROM DB.FOO GROUP BY COLUMN_A UNION ALL SELECT 'COLUMN_B' AS COL_NAME, MAX(LENGTH(COLUMN_B)) AS MAX_LENGTH FROM DB.FOO GROUP BY COLUMN_B UNION ALL ... SELECT 'COLUMN_Z' AS COL_NAME, MAX(LENGTH(COLUMN_Z)) AS MAX_LENGTH FROM DB.FOO GROUP BY COLUMN_Z --UNION ALL |
Play around with the examples given and leave a comment below with any questions you have, I’m always fascinated by SQL generators, how do you use them to help you?