# How to get data from DB into dataset with keys

I'm a Mathematica newbie and I have the following problem: I connect to a Oracle DB and read out a set of variables, further on I want to histogram some of the variables. A Dataset seems to be the right tool, but I produce only datasets without a key via:

DBtest = SQLSelect[db, "TABLE1", {"ID", "VAR_1"}]

Dataset[DBtest]


Result is a Dataset with one column containing ID and the other containing VAR_1 (sorry for the generic names - I can't copy my real work, since it contains sensitive data).

Then I can't use the DBtest[Histogram, "key"] functionality, since there is no "key" available.

Is there anyway to add keys after filling the dataset from the DB? Or shouldn't I use the Dataset class?

• You have to thread the keys "ID" and "VAR_1", like RunnyKine's answer here mathematica.stackexchange.com/questions/59684/… ... Dataset[AssociationThread[First @ data, #] & /@ Rest @ data] – alancalvitti Oct 29 '14 at 19:07
• Perfect! Now it works, many thanks! – Lea Oct 30 '14 at 12:22

For discussion purposes, let's use some data from Mathematica's example databases:

$connection = OpenSQLConnection["publisher"];$dbTest = SQLSelect[$connection, "SALESDETAILS", {"TITLE_ID", "QTY_ORDERED"}] (* {{"PY2002", 75}, {"PY2002", 10}, <<17>> , {"CK4017", 20}, {"CK4016", 20}} *)  We can create a histogram directly from this data without wrapping it in a Dataset: Histogram[$dbTest[[All, 2]]]


As observed in the question, a Dataset with this data carries no column names since the original result set is comprised of lists instead of associations:

$dataset = Dataset[$dbTest]


However, the columns are still accessible by their indices:

$dataset[Histogram, 2]  When we retrieve the original SQL data, we have the option of including a header row by means of the "ShowColumnHeadings" option. We can use the headers from this row to create associations that provide names for our columns: $dataset2 =
SQLSelect[$connection, "SALESDETAILS", "ShowColumnHeadings" -> True] // Inner[#2->#1&, Rest@#, First@#, Association]& // Dataset  Now we can produce histograms using the column names: $dataset2[Histogram, "QTY_ORDERED"]


• This solution works as well. And the definition of \$dataset2, including the SQL connection, is quite elegant. The way to produce histograms without using Dataset at all, was new for me. Thanks! – Lea Nov 17 '14 at 9:24