How to create a drop-down widget in Databricks notebook with choices retrieved using subquery over a table in SQL, python?

[shan_chandra · Accepted solution]

Please refer to the below widget example using SQL

%sql 
DROP VIEW IF EXISTS tempTable;
CREATE temporary view tempTable AS SELECT 'APPLE' as a UNION ALL SELECT 'ORANGE' as a UNION ALL SELECT 'BANANA' as a;
CREATE WIDGET DROPDOWN fruits DEFAULT 'ORANGE' CHOICES SELECT a from tempTable

python example for dropdown with values passed from the table. pass the values from the temp table (in the above example) through data frame df.

df = spark.sql("""select a from tempTable""")
dbutils.widgets.dropdown("fruits3", "ORANGE", [row[0] for row in df.collect()])