My current table includes more than 100 columns and I need to calculate the sum of each column and convert them into rows. Since there are more than 100 column, it is not convenient to use the unpivote clause. Is there any other way to do that? Below is a snapshot of the original table: col1 col2 23 44 33
Bigquery – How to filter records with certain condition
I’m trying to write SQL (in BigQuery) the following in order to get a result that satisfies the following conditions. for ex: my table contains the following data I want to filter out records where it contains only value as “p” from the table. The expected result would be I have tried with t…
Remove duplicate values from comma separated variable in Oracle
I have a variable (called: all_email_list) which contains 3 email address lists altogether. (I found some similar question but not the same with a proper solution) Example: test@asd.com, test2@asd.com,test@asd.com,test3@asd.com, test4@asd.com,test2@asd.com (it can contain spaces between comas but not all the …
SQL query for entries of last N jobs
I have the following table: etc, so basically jobs keep adding entries concurrently I am looking for an efficient query to return all entries of the latest (by time) N jobs So find the first (by time) entry for each job, then using that get the latest N jobs with all their entries So for the above table and N…
Optimising filtering multiple SQL count sub queries with SQLAlchemy
I’m trying to convert raw postgres-SQL into sqlalchemy which uses a count and filter This works great and is very fast (sub 100ms – even when filtering 20 fields) I’ve written the sqlalchemy as (simplified) This produces the following SQL This works but is quite slow (+1 second). I’m n…
how can i move column data to another table and set its id to foreign key column in first table? [closed]
I had table application that one of the columns names allowed_command , then I create another table application_command and want to separate allowed_command column and move to second table ‘…
how do i insert value into a column and referring the previous value at the same time of that column in a Table in Oracle?
I have a table where I have to insert values in Final_value column Explanation of the task First value of the Final_value column will be = 1st value of column1 i.e 5.5 From the 2nd row we will check if column2 is Not Null a) if TRUE , then formula would be previous Final_value column* ( current column1 value/…
How to find the degree of connection in a social network using recursion
Imagine, you are looking at a social network graph of millions of users. Imagine Facebook users who are in different Facebook Groups. Let me give the following example: We start with Jeff. Jeff has a degree of connection with himself of 0. Rohit is in the same Facebook group as Jeff, so his degree of connecti…
How to Unpivot a Struct in BigQuery?
I have a table with an ID and then a struct. I want to turn each element of the struct into a new row with the struct field name being a value in the column Period and the value being the structures value. See table below Query that generated the table: Current data I tried this: But I get this
SQL – updating column based on next row value
I have a table, and the requirement is to update column ‘val’ based on the value in the column ‘val’ in next row (ordered by by id). Table is shown below : I’m able get the select query to get the next value, however the update query is not working. Here is the select query (work…