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
How do to keep latest N rows of each user's and delete the rest of rows in mysql?
I have a user_accounts table with 6 columns:
id, user_id, amount, type, total_balance, created_at 1 101 10 debit 90 2018-09-14 20:10:49 2 101 30 credit 120 2018-09-14 20:10:52 3 102 210 credit 310 2018-09-14 21:10:52 4 102 10 debit 300 2018-09-14 21:10:54 5 103 10 credit 110 2018-09-14 21:10:54 6 104 15 credit 115 2018-09-14 21:11:59
I want to keep latest n rows of each user's and delete the rest of rows.
What is the best way to construct this query in
How to make SQL parse this XML faster
The code below works but it takes 16 minutes to parse the XML from 36,000+ rows in SQL. Is there a way I could change the below code to make it parse the XML faster? I have updated the code below.
select c.claimid, c.adjuddate as lastadjudication, cast(claimedithistory as xml) as xmldata, ca.adjudicationattempts into #kb from claimedithistory ca (nolock) join claim c (nolock) on ca.claimid = c.claimid where adjuddate > dateadd(d, 1, eomonth(getdate(), -3)) order by c.claimid alter table #kb add primary key (claimid, adjudicationattempts) create table #kb2 (claimid char(15) not null,lastadjudication Date not null,attempt int not null, id char(60) null, adjuddate date null) Declare @claimid char(15) Declare @lastadjudication as date declare @xmldata as xml declare @adjudicationattempts as int declare @nextadjudication as int set @nextadjudication = 1 SET NOCOUNT ON DECLARE db_cursor CURSOR FOR SELECT claimid, lastadjudication, xmldata, adjudicationattempts FROM #kb order by claimid desc OPEN db_cursor FETCH NEXT FROM db_cursor INTO @claimid, @lastadjudication, @xmldata, @adjudicationattempts WHILE @@FETCH_STATUS = 0 BEGIN set @nextadjudication = 1 WHILE @nextadjudication <= @adjudicationattempts BEGIN insert into #kb2 (claimid, lastadjudication, attempt, id, adjuddate) select claimid, lastadjudication,xmldata.value('(ClaimEditHistory[sql:variable("@nextadjudication")]/@attempt)', 'int') as attempt, xmldata.value('(ClaimEditHistory[sql:variable("@nextadjudication")]/@userid)', 'varchar(15)') as id, xmldata.value('(ClaimEditHistory[sql:variable("@nextadjudication")]/@datetime)', 'datetime') as adjuddate from #kb where claimid = @claimid and xmldata.value('(ClaimEditHistory[sql:variable("@nextadjudication")]/@datetime)', 'datetime') > dateadd(d, 1, eomonth(getdate(), -3)) and xmldata.value('(ClaimEditHistory[sql:variable("@nextadjudication")]/@userid)', 'varchar(15)') <> 'hsnprbatch' and xmldata.value('(ClaimEditHistory[sql:variable("@nextadjudication")]/@userid)', 'varchar(15)') <> 'TorresM' set @nextadjudication = @nextadjudication + 1 END FETCH NEXT FROM db_cursor INTO @claimid, @lastadjudication, @xmldata, @adjudicationattempts END CLOSE db_cursor DEALLOCATE db_cursor select * from #kb2
In SQL, group by in INT ranges
I have following sql table;
I need to group rows by intx value however, this intx values is dynamic so, there is no specific range of values.
id, intx, name "1" "70" "x7" "2" "99" "x100" "3" "100" "x100" "4" "101" "x100" "5" "69" "x7" "6" "50" "x5"
I want to group this table with use intx.
But, I want to group values of intx between of (intx+1) and (intx-1)
forexample; intx value = 100, 101 and 99 must be one group.
use following commands;
select a.intx, b.intx,a.name,b.name,MAX(a.intx,b.intx),MIN(a.intx,b.intx) from t1 a inner join t1 b on a.intx <=b.intx+1 and a.intx >= b.intx-1 and a.intx<>b.intx group by MAX(a.intx,b.intx)
but give me following result ; a.intx, b.intx, a.name, b.name, MAX, MIN
a.intx b.intx a.name b.name MAX MIN "69" "70" "x7" "x7" "70" "69" "100" "99" "x100" "x100" "100" "99" "101" "100" "x100" "x100" "101" "100"
didn't put 101 into the second group.
access restful service from saas application in oracle cloud
I am looking for how to get data from oracle cloud using restful service and saas application. I have tried a lot of links but that results in a separate platform or a different web services but I want restful web service and saas. Please guide me. Thanks!
Linking a classic page to a fluid page in peoplesoft
I have a problem with my classic page that is linked to my fluid page. When you click on the link, the classic page is somewhat missarranged, though this is not the case with the page in the Application Designer. It was ok the previous days but became somewhat missarranged after some patching by oracle.
Any ideas what caused this and how to address this?
Really appreciate any help I can get.
Create Dynamic SQL Where clause in BI Publisher 12c
How can I modify SQL dynamically in BI Publisher 12c based on user's choice?
I have a requirement where I need to apply multiple BIP parameters. Now if user pass something in parameter, it makes sense to apply it but if user let 'All' values to pass through a prompt it make SQL very slow.
Here is an example of how we are using a parameter in Data Model-
SELECT DEPTNO FROM DEPT WHERE 1=1 AND IN DEPTNO IN (: p_deptno)
Now I need to remove
AND IN DEPTNO IN (: p_deptno)section when user chooses 'All' to see all the data (removal is a must). Imagine I have many filters and I need to remove all of them when not selected.
I have seen some help for old BIP in here but nothing for new 12 version (per below link: https://only4techies.wordpress.com/2009/12/12/how-to-use-conditional-sql-statements-in-bi-publisher/)
I am using Oracle for my database.
- Show the error message in a popup alert rather then on status bar
total number in field cannot exceed a value
How can i tell oracle apex that the sum of a field (personal) cannot exceed 5 (5 being the total number of the field). so example somebody could take up to 5 personal days but cannot exceed it other than that they have to use vacation
calculating dates in oracle apex
i have two(2) tables
employee name, contract start date, contract end date , total vacation days
employee_name,leave start date and leave end date (date pickers), leave amount(number field).
This will show in a calendar when the employee takes his leave (personal,vacation)
i have 3 scenarios i would like help with.
(1) how can i show holidays on the calendar
(2) how can i exclude holidays and weekends when i use my date picker field to calculate days (i have a pl/sql that automatically calculate the leave amount) and
(3) how can i show a notification that you need to take all your vacation one month before your contract expires.