2D SELECT in SQL

Question

I have a table which stores data in the following format:

Time  | WidgetId | WidgetData
00:00      1           12
00:00      2           5
00:00      3           7
00:01      1           9
00:01      2           13
...       ...          ...
23:59      3           1

Essentially, for every minute of the day it stores some data about each Widget. The number of widgets is variable, but info about them is stored in another table so is accessible.

I need to produce the results in the following format:

Time  Widget1    Widget2    Widget3
00:00    12        5           7
00:01    9         13         ...
...      ...       ...        ...
23:59    ...       ...         1

Is this possible just in SQL? I'm using SqLite and can achieve the results with some scripting but it would be ideal if I could get this data out just using a database SELECT.

score 2 · Answer 1 · edited May 23 '17 at 12:29

You can do this using aggregates:

SELECT  Time,
        SUM(CASE WHEN WidgetId = 1 THEN WidgetData ELSE 0 END) AS Widget1,
        SUM(CASE WHEN WidgetId = 2 THEN WidgetData ELSE 0 END) AS Widget2,
        SUM(CASE WHEN WidgetId = 3 THEN WidgetData ELSE 0 END) AS Widget3
FROM    T
GROUP BY Time;

Example on SQL Fiddle

As far As I know Sqlite does not support dynamic SQL, so if you have an unknown number of widgets (hence an unknown number of columns) you would need to build your query in the application layer before sending it to Sqlite. Without knowing your scripting language, or how you decide which widgets to include I can't really advise further.

In the most general terms you would need to retrieve a list of all widgetIDs you want to include, then loop through these using something like:

string sql = "SELECT Time";
for (int i = 0; i < widgetIds.Length; i++
{
    sql += ",SUM(CASE WHEN WidgetId = " + i.ToString() + " THEN WidgetData ELSE 0 END) AS Widget" + i.ToString()
}
sql += " FROM T GROUP BY Time;";

N.B. This is very generic, and should be seen as pseudo code rather than an actual solution

There are probably many more generic solutions on Stackoverflow alone if you search for Sqlite Dynamic Pivot, and possibly add in your scripting language for more tailored solutions.

Thanks for the answer. I had something similar to the pseudo code implemented in python already, but wondered whether SqLite would be able to do it itself. Seems like the answer is a resounding no! — Chris, Nov 19 '13 at 14:20

score 1 · Answer 2 · answered Nov 19 '13 at 11:23

You can use a single SELECT if you know before how many widgets you have. As you don't, you need one more query to get all widgets.

 SELECT DISTINCT WidgetId FROM table;

Then, you may dynamically build you query like this:

 SELECT Time, Widget1.WidgetData, Widget2.WidgetData, ... -- Fill with rows from previous query
 FROM (
     SELECT DISTINCT Time FROM table
     LEFT JOIN table AS Widget1 ON table.Time=Widget1.Time AND Widget1.WidgetId=1
     LEFT JOIN table AS Widget2 ON table.Time=Widget1.Time AND Widget1.WidgetId=2
     ... -- Fill using IDs from previous query
 )

2D SELECT in SQL

2 Answers2