Oracle SQL - Combining multiple rows of same table into own row

Question

I have a table that looks like this one on SQL Fiddle: http://sqlfiddle.com/#!4/0fabe0/2/0

The table holds 12 rows. 6 belong to one "set", the other 6 belong to another "set". They are grouped by the identifier column. In reality, the table I want to query holds more rows and columns.

I want to make a select that gives me one row per identifier. Then it should show the insertTime and then for each "step" the "Yes" value and at the end for each "step" value "No" value.

I think I need to work with a combination of group by, distinct and joins to achieve this but I could not figure out how.

Here is how it should look like:

+------------+------------------+----------+----------+----------+---------+---------+---------+
| identifier |    insertTime    | yesStep3 | yesStep2 | yesStep1 | noStep1 | noStep2 | noStep3 |
+------------+------------------+----------+----------+----------+---------+---------+---------+
|        123 | 08.04.2018 13:37 | 13.3     | 12.2     | 11.1     | 13      | 13.3    | 14.1    |
|        124 | 08.04.2018 13:40 | 14.14    | 13.13.   | 12.12.   | 10.1    | 11.11   | 9.9     |
+------------+------------------+----------+----------+----------+---------+---------+---------+

Thank you! I am using Oracle 10g and thus cannot use the PIVOT function. Anyhow, I will now try to find any alternatives by browsing Google for "Oracle 10g PIVOT". — Malawirel
– Malawirel, Commented Apr 9, 2018 at 12:20
If you don't have the PIVOT function then you can use conditional aggregation (as illustrated in @MT0's answer below). — David Faber
– David Faber, Commented Apr 9, 2018 at 15:31

MT0 · Accepted Answer · 2018-04-09 12:54:01Z

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE testTable(
  identifier NUMBER(3,0),
  insertTime timestamp,
  yesNo      NUMBER(1,0),
  stepNumber NUMBER(1,0),
  value      NUMBER(5,2)
);

INSERT INTO testTable (identifier, insertTime, yesNo, stepNumber, value)
  SELECT 123, TIMESTAMP '2018-04-08 13:37:00', 0, 1, 13.0 FROM DUAL UNION ALL
  SELECT 123, TIMESTAMP '2018-04-08 13:37:00', 0, 2, 13.3 FROM DUAL UNION ALL
  SELECT 123, TIMESTAMP '2018-04-08 13:37:00', 0, 3, 14.1 FROM DUAL UNION ALL
  SELECT 123, TIMESTAMP '2018-04-08 13:37:00', 1, 1, 11.1 FROM DUAL UNION ALL
  SELECT 123, TIMESTAMP '2018-04-08 13:37:00', 1, 2, 12.2 FROM DUAL UNION ALL
  SELECT 123, TIMESTAMP '2018-04-08 13:37:00', 1, 3, 13.3 FROM DUAL UNION ALL
  SELECT 124, TIMESTAMP '2018-04-08 13:40:00', 0, 1,  9.9 FROM DUAL UNION ALL
  SELECT 124, TIMESTAMP '2018-04-08 13:40:00', 0, 2, 10.10 FROM DUAL UNION ALL
  SELECT 124, TIMESTAMP '2018-04-08 13:40:00', 0, 3, 11.11 FROM DUAL UNION ALL
  SELECT 124, TIMESTAMP '2018-04-08 13:40:00', 1, 1, 12.12 FROM DUAL UNION ALL
  SELECT 124, TIMESTAMP '2018-04-08 13:40:00', 1, 2, 13.13 FROM DUAL UNION ALL
  SELECT 124, TIMESTAMP '2018-04-08 13:40:00', 1, 3, 14.14 FROM DUAL;

Query 1:

SELECT identifier,
       insertTime,
       MAX( CASE WHEN yesNo = 1 AND stepNumber = 1 THEN value END ) AS yesStep1,
       MAX( CASE WHEN yesNo = 1 AND stepNumber = 2 THEN value END ) AS yesStep2,
       MAX( CASE WHEN yesNo = 1 AND stepNumber = 3 THEN value END ) AS yesStep3,
       MAX( CASE WHEN yesNo = 0 AND stepNumber = 1 THEN value END ) AS noStep1,
       MAX( CASE WHEN yesNo = 0 AND stepNumber = 2 THEN value END ) AS noStep2,
       MAX( CASE WHEN yesNo = 0 AND stepNumber = 3 THEN value END ) AS noStep3
FROM testTable
GROUP BY identifier, insertTime

Results:

| IDENTIFIER |            INSERTTIME | YESSTEP1 | YESSTEP2 | YESSTEP3 | NOSTEP1 | NOSTEP2 | NOSTEP3 |
|------------|-----------------------|----------|----------|----------|---------|---------|---------|
|        123 | 2018-04-08 13:37:00.0 |     11.1 |     12.2 |     13.3 |      13 |    13.3 |    14.1 |
|        124 | 2018-04-08 13:40:00.0 |    12.12 |    13.13 |    14.14 |     9.9 |    10.1 |   11.11 |

Collectives™ on Stack Overflow

Oracle SQL - Combining multiple rows of same table into own row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related