Oracle newbie here just trying to learn something. I have a query that returns two rows per ID: Current Output Instead of two rows, how can I put the two varying values in the B1_CHECKLIST_COMMENT column into two separate columns? Visually, here’s what I’m looking to achieve: Desired Output I’m using Oracle version 12.1. Answer You can use PIVOT: Or
Tag: oracle
Search List Stored in Column and Eliminate Row with SQL
I have a table (MYTABLE) in an Oracle database that has a column which stores a list of items. Something like: ITEM_ID ITEMS 5476301 B, L, M, PP, S, TT 5579504 B, L, M, PP, S, TT 5579504 B, L, PP, S, TT 4005953 L 4064254 L, M 4121291 PP 4162254 M 4189256 M, PP 4255971 M, PP I am
SQL – how to filter by timestamp looking for anything after a certain time on a previous day
I need to filter a query results to only bring in rows of data that were created after 5pm the previous BUSINESS DAY up until current time, with business day being Monday to friday. I can filter for previous business day, and I can filter by timestamp…but I can’t combine the 2 in order to filter after 5pm previous business
How to using oracle distinct and order siblings by at the same time?
the distinct broken the order siblings by,how can i use them at the same time? such as select distinct * from table xxx starts with … connect by id=pid order siblings by field here is the test sqls executed with different results here is the table Answer You can use ORDER SIBLINGS BY in an inner query and then use
Capture string in between
How can I capture data in between a series of strings? I have the following data in a column: Basically, remove all before “INCIDENT TYPE:” and after “External ALARM ID:” “INCIDENT TYPE:” has no specific number of characters. And I only want to display the incident type value Answer Assuming your messages have the fixed format of: we can use
Show data from table even if there is no data
I have 3 tables: Data in Compania table: Data in Periodo table: Data in VAC_PERIODOCIA table: I want to show all the companies (COMPANIA) and the value in (vac_aplica column) searching by Periodo, whether or not they are registered. I tried this: Result: What I want is this: Answer First of all, the question is a mess: tables and columns
Deleting duplicates on combination of two columns in oracle
I have a table for example : Port Table S No A Port B port 1 80 100 2 90 110 3 100 80 4 94 106 I want to delete record no. 3 as it is having same combination as record no. 1 how to do this in oracle ? Answer You can use a single MERGE statement and
Set new field priority in SELECT SQL
I have a table of bills with the following structure: What I need is to select all data from table and set additional field “Priority” based on a sum of bill. First 2 rows get priority 1, next 2 rows get priority 2, others get 0: In addition table contains data about bills from various days (field payment_date) and this
can’t set a foreign key on oracle sql
Here is my code: and it gave me this error: What is wrong with parenthesis in my code? this only happens when i set foreign key. Note: i am using SQL cmd from Oracle Database 11g Express Edition. I’ve tried making theses tables on MySQL and it worked fine. Answer No “FOREIGN KEY” keyword is used for inline constraints. So,
How do I conditionally select a unique value in SQL?
I’ve been tasked with returning only rows with unique IDs but returning a row for every ID in SQL. How would I go about this? Logic: For primary row, select where JOB_INDICATOR = ‘P’. If there are multiple rows, then use the record where PRIM_ROLE_IND = ‘Y’. If there are still multiple then select the lowest numbered EMPL_RCD starting at