CODEWITHSUNDEEP
sqloracle-databasepivot
Jamie Joe
Jamie Joe

Reputation: 43

Returning multiple values on the same row, based on a value on the same table

I've been quite stumped for a while, been trying to get results from one of our tables based on other results, and not returning more than one row for each value, it's hard to explain for me so I will give an example.

We have a values for example, APPLES. Every apple has its own serial number, SERIAL. for some APPLES, there are multiple (up to 6) SERIAL values.

ID      APPLE       SERIAL
2052    5035        1
2055    5035        4
2058    5035        8
2070    5040        5

In my results, I want to return each APPLE once, and all serial's assigned to that, for example:

APPLE   SERIAL_1    SERIAL_2    SERIAL_3    SERIAL_4
5035    1           4           8
5040    5

I have tried to make a query which basically joins the same table 4 times, saying serial_x not in (other serials). The problem with this is that it will produce multiple results like this:

APPLE   SERIAL_1    SERIAL_2    SERIAL_3    SERIAL_4
5035    1           4           8
5035    1           8           4
5035    4           1           8
5035    4           8           1
5035    8           1           4
5035    4           4           1
5040    5

This is obviously because apple 5035 with a different serial occurs multiple times in the table, and it gets all possible combinations of the three serials assigned to it. I'm sure there is an easy way to do this, but I've been trying for a long time and haven't been able to produce a good result. Can anybody assist?

Upvotes: 1

Views: 7019

Answers (5)

user330315
user330315

Reputation:

A bit late to the game, but as there is no answer using the PIVOT clause in Oracle, it might be interesting nevertheless.

SELECT *
FROM (
  SELECT apple, serial
  FROM fruits
) t
PIVOT ( 
  max(serial) for serial in (1,2,3,4,5,6)
)

SQLFiddle example: http://sqlfiddle.com/#!4/3cede/2

Upvotes: 0

Craig
Craig

Reputation: 5820

I would do something like this:

with data as (
    select 2052 id, 5035 apple, 1 serial from dual union all
    select 2055 id, 5035 apple, 4 serial from dual union all
    select 2058 id, 5035 apple, 8 serial from dual union all
    select 2070 id, 5040 apple, 5 serial from dual
)
select
    apple,
    serial_1,
    serial_2,
    serial_3,
    serial_4,
    serial_5,
    serial_6
from (
    select
        apple,
        serial as serial_1,
        lead(serial,1) over (partition by apple order by serial) as serial_2,
        lead(serial,2) over (partition by apple order by serial) as serial_3,
        lead(serial,3) over (partition by apple order by serial) as serial_4,
        lead(serial,4) over (partition by apple order by serial) as serial_5,
        lead(serial,5) over (partition by apple order by serial) as serial_6,
        row_number() over (partition by apple order by serial) rn
    from data
)
where rn = 1;

Obviously you don't need the WITH block since you can use your real table, so your query would start at the SELECT.

This will give the following output:

 APPLE   SERIAL_1   SERIAL_2   SERIAL_3   SERIAL_4   SERIAL_5   SERIAL_6

  5035          1          4          8
  5040          5

Upvotes: 0

DCookie
DCookie

Reputation: 43533

Oracle 11g has the LISTAGG function that seems to do what you're looking for. I don't have 11g here, but the following should be close:

SELECT apple, listagg(serial, ',') WITHIN GROUP (ORDER BY serial) "Serial Numbers"
  FROM tbl
 GROUP BY apple;

You don't get the nice column headers for each serial item, but it should work.

Upvotes: 0

ghbarratt
ghbarratt

Reputation: 11711

You could try using the GROUP_CONCAT aggregate function and GROUP BY APPLE

SELECT 
    a.APPLE,
    GROUP_CONCAT(DISTINCT s.SERIAL) AS serials
FROM 
    apples a
    LEFT JOIN apples s ON s.APPLE = a.APPLE
GROUP BY a.APPLE


Tested in MySQL:

mysql> select * from apples;
+------+-------+--------+
| ID   | APPLE | SERIAL |
+------+-------+--------+
| 2052 |  5035 |      1 |
| 2055 |  5035 |      4 |
| 2058 |  5035 |      8 |
| 2070 |  5040 |      5 |
+------+-------+--------+
4 rows in set (0.00 sec)

mysql> SELECT 
    ->     a.APPLE,
    ->     GROUP_CONCAT(DISTINCT s.SERIAL) AS serials
    -> FROM 
    ->     apples a
    ->     LEFT JOIN apples s ON s.APPLE = a.APPLE
    -> GROUP BY a.APPLE;
+-------+---------+
| APPLE | serials |
+-------+---------+
|  5035 | 1,4,8   |
|  5040 | 5       |
+-------+---------+
2 rows in set (0.00 sec)

mysql>

Upvotes: 0

Marco
Marco

Reputation: 57583

You could try this, it's not elegant but it works:

SELECT DISTINCT t1.apple, 
    (SELECT serial FROM your_table
     WHERE apple = t1.apple
     ORDER BY serial LIMIT 0,1) serial_1,
    (SELECT serial FROM your_table
     WHERE apple = t1.apple
     ORDER BY serial LIMIT 1,1) serial_2,
    (SELECT serial FROM your_table
     WHERE apple = t1.apple
     ORDER BY serial LIMIT 2,1) serial_3,
    (SELECT serial FROM your_table
     WHERE apple = t1.apple
     ORDER BY serial LIMIT 3,1) serial_4,
    (SELECT serial FROM your_table
     WHERE apple = t1.apple
     ORDER BY serial LIMIT 4,1) serial_5,
    (SELECT serial FROM your_table
     WHERE apple = t1.apple
     ORDER BY serial LIMIT 5,1) serial_6
FROM your_table t1

Upvotes: 1

Related Questions