I have a very large set of data that is formed similar to this: I want to produce report output that drops the table headers (field1, field2, field3) and instead use data row1 as headers in output. I am able to do this nicely in SQLPlus and SPOOL to CSV, but I want to do everything in PLSQL so that
Tag: sql
Improve SQL query to find range between start and end date
I’m working with a database called international_education from the world_bank_intl_education dataset of bigquery-public-data. My aim is to plot a line graph with countries who have had the biggest and smallest change in Population growth (annual %) (one of the indicator_name values). I have done this b…
Oracle GROUP BY clause is having issue
SQL Query in oracle database is: SELECT max(employee_id) from bank_data group by job_type having job in (‘Engineer’,’Artist’); My table data is: I get below error: ORA-00979: not a GROUP BY …
Hive – find 2 characters anywhere in the string/row – RLIKE
How do I get the data for ONLY “_WA” data assigned to “USA_RBB_WA_BU”? However the column I look at has rows that contain _WA and _SA (USA_CA_SAWANT) I used, select…. …
Insist on number of rows on left side of join
I have an issue where I want to join two tables where there is 1 record on the left and many on the right side but only need information from the right that exists in all of those records – in other words, insist on only getting 1:1 not 1:n in my results. Example, if I run this statement below,
JOIN on Subquery Based on Date Evaluation
I have a query that includes a subquery that references one of the tables that my query joins on, but I also need to do an evaluation on the field returned from the subquery in my WHERE clause. Here’s the current query (rough example) – My subquery is currently returning the latest date from the c…
Linq tolist() count returns different value from translated sql
I have a situation where the translated sql direct form Visual Studio is returning a different number of records from the Linq. I am expecting 4 items but in the count() of the resulting list I only have 1. This is the case despite creating more records – it always returns 1. Strange thing is I have the…
Cumulative Sum When rows have the same value sql sql server
I’d like to do cumulative sum of the column SubTotal to get a cumulative RunningTotal column. But I noticed that if rows have the same values, the cumulative sum doesn’t work. e.g. In the below example, the row 5 and row 6 have the same SubTotal value of 838.9178. Because of that, the RunningTotal…
Custom SQL response (JOIN TABLE)
I have two tables, table1 and table2 I want to link the two tables with the id, by grouping the information in one and the same answer. table1: ╔════╦══════════════╦ ║ id ║ product_id ║ ╠════╬═══…
Convert from lateral view to case statements in hive
I need to place below code in case statement: I have tried: But getting error. Answer For the provided SQL on table tab1, the actual logic is like: Explode field secondary.tertiary, alias it as lv, which results in a temporary result set (table) tab2; A join-like operation to concatenate tab2’s fields b…