I have an xml which I need to read using Snowflake SQL. I need to use the attribute name and lits value to fetch the data. For example -> id and “bk101” to fetch the contents. I am currently using below query- In this case I am passing the index 0-4 to fetch the data is not producing correct re…
BigQuery: How to aggregate records in a STRUCT or JSON field?
I want to aggregate a pair of columns as a dictionary of key,value pairs to construct a STRUCT or a JSON STRING over multiple records. Currently my implementation leverages the STRING nature of JSON & STRING_AGG to build such a JSON value: Which results in the following: Is there a more readable approach?…
SQL query to get the aggregate results using row_number over partition by
I have table like the below car_id part_name diagnosis car_1 windscreen good car_1 brakes good car_1 tyres good car_1 wipers good car_2 windscreen good car_2 brakes good car_2 tyres …
Recursive SQL query (parent-child) to include total downline records per record
I have a table that has a parent – child structure. I managed to bring this structure sorted by creating a field called “Path” with the first name of the agent, so this query: WITH cte(…
Is it possible to iterate over a list of strings and conditionally query an oracle database table based on the current value using SQL or PL/SQL?
Is it possible to query conditionally based on the current value in a list of strings using either SQL or PL/SQL? I am working with an Oracle Database if that helps? Example of desired functionality (…
How do I pass a variable to a stored procedure to update a row in another database?
I need to get a value from one database for a customer and update another database with that value. The procedure below works but I need to have it go through table2 and update every customer in table1 with a matching CustomerID. I hate to use the word loop through but as I said, I am very new to this
Converting Multiple Rows to Columns
How would I convert rows into columns? In this case, taking the columns of Reason and Weight and expanding the values where more columns are created? The column Reason will only have four values (…
How can I create new column in sql based on previous date?
Output Answer You can try with LAG() as suggested by @Akina EDIT i see you are in mysql just now, you can try with this answer as well
Filtering empty rows from Mysql SELECT query issue
I have a SELECT query as below: This query gives me an output like this: My question is, how to filter the 0 due records from due_balance and modify the query to get the rest? Expecting result should be: I tried it in this way, but it doesn’t work for me. Answer Use HAVING Clause or use subquery then ad…
How to create table/view with data from another SQL server?
I have a database managed by a third party vendor and have access to read-only for the tables and views. I would like to be able to create a table from this database in a remote server. Is this possible? If so, how do I do it? Thanks in advance. Hey this is what I got from SELECT @@VERSION Microsoft