DPSQLStudio Data Tools — Sheets, VLOOKUP, Pivot & Multi-sort

A spreadsheet layer on top of Oracle

DPSQLStudio is a free, lightweight Oracle SQL client for Windows. Past the editor and results grid sits a Data menu that treats a query result like a spreadsheet — so you can enrich, reshape, sort and compare data without leaving the app or hand-writing the SQL. This post walks the core of that suite on a real SALES_ORDERS table: Sheets, VLOOKUP, Pivot, and Multi-sort. Every screenshot here is the actual app (v0.1.0 Beta), connected to an Oracle Autonomous database as ADMIN@dpsqltest_high.

1. Sheets — snapshots the Data tools work on

Most Data tools operate on Sheets: named, in-memory snapshots of a result. Run a query, then Data ▸ Save Result as Sheet and give it a name. Manage Local Sheets lists everything you’ve captured so you can view, rename or delete — they’re the building blocks VLOOKUP and Compare match against.

2. VLOOKUP — enrich a table from a lookup sheet

Goal: pull product details onto every order. The base sheet is SALES_ORDERS; the lookup sheet is the products list. In Data ▸ VLOOKUP you pick the base dataset, the lookup dataset, the key to match on, and the columns to bring back.

Run it, and PRODUCT_NAME, CATEGORY and UNIT_PRICE are appended to every order row — resolved by the product key, not pasted:

3. Pivot — cross-tabulate in two clicks

With the enriched sheet in hand, Data ▸ Pivot builds a cross-tab. Here: rows = CATEGORY, columns = REGION_ID, value = AMOUNT, aggregate = Sum — total sales by category across regions.

The result is a compact category × region matrix of summed amounts:

4. Multi-sort — order by several keys at once

Data ▸ Multi-column Sort sorts the current result by one or more columns with per-column direction — the kind of ORDER BY you’d otherwise type out, applied to the live grid (and saved as a sheet if you want).

Try it yourself on tiny data

Want to reproduce the mechanics on data small enough to check by eye? This independent example uses an 8-row employee table and a 3-row department table. I verified its output in SQLite, so you know the tool is doing a real keyed lookup and a real grouped sum — not a cosmetic merge.

CREATE TABLE demo_emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(40),
  job VARCHAR2(20), dept_id NUMBER, salary NUMBER);
INSERT INTO demo_emp VALUES (100,'King','PRESIDENT',10,24000);
INSERT INTO demo_emp VALUES (101,'Kochhar','MANAGER',10,17000);
INSERT INTO demo_emp VALUES (102,'De Haan','MANAGER',20,17000);
INSERT INTO demo_emp VALUES (103,'Hunold','ANALYST',20,9000);
INSERT INTO demo_emp VALUES (104,'Ernst','ANALYST',20,6000);
INSERT INTO demo_emp VALUES (105,'Lorentz','CLERK',20,4200);
INSERT INTO demo_emp VALUES (106,'Mourgos','MANAGER',30,5800);
INSERT INTO demo_emp VALUES (107,'Rajs','CLERK',30,3500);
CREATE TABLE demo_dept (dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(30));
INSERT INTO demo_dept VALUES (10,'Executive'),(20,'IT'),(30,'Shipping');
COMMIT;

VLOOKUP dept_id→dept_name gives Executive, Executive, IT, IT, IT, IT, Shipping, Shipping. Pivot SUM(salary) by dept × job gives:

Verified independently. Recomputing both in SQLite from these eight rows reproduces the tables exactly — including IT’s ANALYST cell of 15,000 (Hunold 9,000 + Ernst 6,000).

Coming up

Next in the DPSQLStudio series: the Hierarchy Explorer (employee org charts and bill-of-materials explosions) and Compare Datasets (spotting exactly what changed between two snapshots).

DPSQLStudio v0.1.0 (Beta) — a free Oracle SQL client for Windows. Screenshots are from the live app; demo data is fictional.

Comments (0)