Oracle SQL - Multiple rows into one field
I have a table like this:
I need a query (no PL/SQL) that shows this output:
So for each Product ID I want the distinct values of Delivery Type in the field Delivery Type.
Is there a way to get this result through a "simple" query in Oracle?
I am using Oracle 11g.
Thanks in advance !
listaggafter getting the distinct delivery types per product id. (Note that there is a 4000 character limit for the aggregated string.)
select product_id,listagg(delivery_type,'/') within group (order by delivery_type) from (select distinct product_id,delivery_type from tbl) t group by product_id
See also questions close to this topic
(My)SQL - For showing last Inbox of Messages
So hopefully the question is not asked, but I didn't find it.
My example table looks like this: https://imgur.com/a/BBykp
For my SQL I know for example the user ID of 1 and I want to show the Inbox of my Messanger for that I need to know if User ID 1 received a message or was the sender of a message.
sender_id receiver_id 1 2 3 1 1 4 12 1
Should give me four Inbox results for ID 1.
So I currently stuck with
SELECT * FROM chats AS tm WHERE tm.id IN (SELECT MAX(id) FROM chats WHERE sender_id = 1 OR receiver_id = 1 GROUP BY receiver_id)
This gets me: https://imgur.com/a/P665T
You'll see that I don't need ID 2, but I care for ID 3, because it's the latest message of that conversation.
So how can I decide with SQL that I want the latest of sender_id 1 or receiver_id 1?
MariaDB View not working but statement does
My new hosting provider is running MySQL Version 10.0.31-MariaDB-cll-lve
I have a view that was working fine in MySQL 5.6 but does not work in MariaDB.
I have created a simple cut down version just to show what is causing an error.
I can create the view but I can not use it:
CREATE VIEW `test_date` AS select (case when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') then '2018-06-30' else NULL end) - interval 4 month
The error I get when trying to open:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '21:05:05 between 2018-01-01 00:00:00 and 2018-06-30 23:59:59) then '2018-06-30' ' at line 1
I can't see anything wrong and it works fine on normal MySQL servers.
I have tried removing the '- interval 4 month' and it works fine:
CREATE VIEW `test_date` AS select case when (now() between '2018-01-01 00:00:00' and '2018-06-30 23:59:59') then '2018-06-30' else NULL end
I have tried replacing the dates with simple numbers and it works fine:
CREATE VIEW `test_date` AS select (case when (3 between 1 and 5) then '2018-06-30' else NULL end) - interval 4 month
So what is the real problem here? I am stumped.
MySQL - Average of Unique rows
If I have three columns:
id, user, points
My data is:
+-------+------------------+-------------+ | id | user | points | +-------+------------------+-------------+ | 1 | A | 100 | +-------+------------------+-------------+ | 1 | A | 200 | +-------+------------------+-------------+ | 2 | B | 300 | +-------+------------------+-------------+ | 2 | B | 400 | +-------+------------------+-------------+
I would like to have the average of ONLY the max points of each user. For this exmple I want to get as results: 300 points ((200+400)/2).
When I use the following Mysql query, I get: 250:
SELECT avg(points) FROM table
Can I store an Oracle database in a different location to the installation?
I have 1) a Windows server with a limited amount of disk space, and 2) a linux SAN drive with unlimited disk space.
Can I install Oracle on the Windows server, and restore the .dmp files but storing the data on the SAN drive?
How can I import one column from excel to multiple columns on db?
I ve data on excel which size is bigger than 4000. In my column on db are
Son I want to insert data from excel to DB. If data is bigger than 4000 , split data to that columns
thanks in advance
How to Update tables based on self join
Update table based on self join on customer_po = ID
ID Invoice Date Delivery_date Customer_po 123 01-01-2018 null null 125 10-01-2018 null 123
I want Output record like below in Oracle SQL
ID Invoice Date Delivery_date Customer_po 123 01-01-2018 01-01-2018 null 125 10-01-2018 01-01-2018 123
Oracle APEX Search Engine
I'm using Oracle APEX 4.2.6 and Oracle DB 11gR2
I've an interactive report showing the list of clients.
The end user can modify the Name of the client.
My issue is that I have to find a way to allow the end user to find the modified client by seraching it with his old name.
For example, the end user modify the name of client from OLD NAME to NEW NAME
In the serach engine of the interactive report, the end users must be able to find the client by serching it by its old name OLD NAME
Is there a way to manage this situation on the APEX side or Database side.
Error trying to use Oracle's JPublisher
I'm trying to use Oracle's JPublisher to access types and functions defined in a package on Oracle 11g.
At the moment, when I try to execute the jpub command, I receive the following error:
cmd_console> jpub -user=scott/tiger -input=demoin -dir=demo -package=corp
Error: Could not find or load main class oracle.jpub.Main
I followed the Oracle tutorial, however I'm stuck on this problem.
how can i convert this to left outer join?
SELECT dept_code "Dept Code", INITCAP(dept_name) "Dept Name", (SELECT COUNT(*) FROM SS_EMPLOYEES WHERE dept_dept_code = SS_DEPARTMENTS.DEPT_CODE AND resign_date IS NULL ) "Emp Count" FROM SS_DEPARTMENTS;
what's missing here?
SELECT DISTINCT dept.dept_code "Dept Code", INITCAP (dept.dept_name) "Dept Name", COUNT (empl.dept_dept_code) "Emp Count" FROM SS_DEPARTMENTS dept LEFT OUTER JOIN SS_EMPLOYEES empl ON ( dept.DEPT_CODE = empl.dept_dept_code AND empl.resign_date IS NOT NULL) GROUP BY dept.dept_code, dept.dept_name