SQL: aggregate and condense

Question

I could do with something I can’t figure out how to do. I have a simple query that I need to aggregate and condense. Here is the base script:

select t.serial, t.make, t.model, t.upgrade_date, t.software_version             
FROM table t
Order by t.serial

These are electrical devices that have been software upgraded (up to 3 times). The script above, of course, will provided multiple rows for the same device but with varying software versions while the make and model remain constant. I understand how to aggregate this to a single row using max to, for example, show the latest software version. However, what I need is to aggregate and have a condensed column showing EVERY software_version (in order) the device has been at.

It could look like this:

+------------------------------------------+ 
| Serial | Make | Model | Version_history  |
+------------------------------------------+
| A        Terra  1       1.1, 1.9, 2.1    |
+------------------------------------------+

Alternatively, and possibly better, it would be fine to show each software_version as a column header with ‘upgrade_date’ if the device was at that version. Actually, it would be useful to know both methods.

This could look like this:

+------------------------------------------------------------------------+
| Serial   Make   Model   1.1           1.9           2.1            2.3 |
+------------------------------------------------------------------------+
| A        Terra  1       01-Jan-2019   17-Jun-2019   31-Dec-2019        |
+------------------------------------------------------------------------+

No doubt this is really simple but I can’t for the life of me figure it out.

The first output can easily be achieved using `listagg()` – Jan 16 '20 at 08:45 — , Jan 16 '20 at 08:45
Brilliant. That was quite simple. Thank you. – Sid Jan 16 '20 at 09:08 — Sid, Jan 16 '20 at 09:08

MT0 · Accepted Answer · 2020-01-16T09:54:09.027

Use LISTAGG:

select serial,
       make,
       model,
       LISTAGG( software_version, ',' ) WITHIN GROUP ( ORDER BY upgrade_date )
         AS Version_history,
       LISTAGG( upgrade_date, ',' ) WITHIN GROUP ( ORDER BY upgrade_date )
         AS Version_dates
FROM   table
GROUP BY serial, make, model
Order by serial

Alternatively, and possibly better, it would be fine to show each software_version as a column header with ‘upgrade_date’ if the device was at that version.

If you know the versions then you can do this with a PIVOT:

SELECT serial,
       make,
       model,
       "1.1",
       "1.9",
       "2.1",
       "2.3"
FROM   table
PIVOT ( MAX( upgrade_date ) FOR software_version IN (
  '1.1' AS "1.1",
  '1.9' AS "1.9",
  '2.1' AS "2.1",
  '2.3' AS "2.3"
) )

db<>fiddle

If you don't know the versions then this would be a dynamic pivot and is not a simple SQL query. You would need to use a two stage process to first generate a dynamic SQL query and then second use EXECUTE IMMEDIATE to run the query. In general, you shouldn't use dynamic pivots and, instead, transpose rows to columns in whatever programming interface you're using to access the database (Java, Python, C#, etc.).

Thank you so much. That worked precisely how I wanted. For now there are only 3 software_vers but long term there will be many. The solution works for now and I'll brush up on dynamic pivots. — Sid, Jan 16 '20 at 12:52
@Sid Don't use dynamic pivots in Oracle. Just use `SELECT serial, make, model, software_version, upgrade_date FROM table` and then transpose it in whatever middle tier software (JSP, PHP, Python, C#, ColdFusion, etc) to pivot rows to columns there; it will probably be a lot simpler to achieve and maintain if you do it there rather than in Oracle (which likes to have a well-defined and static set of columns). — MT0, Jan 16 '20 at 12:57

SQL: aggregate and condense

1 Answers1