Given a dictionary like column in a dataset, I want to grab the value from a key given that the value from another key is satisfied. Example: Say I have a column ‘statistics’ in a dataset, where each data row looks as: I want to get the value of ‘eye’ whenever hair is ‘black̵…
Tag: sql
Iterate through a list of servers in SQL Server
I have a script similar to below and get: Could not find server ‘@CURSERVER’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. I am trying to get SQL Server Agent jobs from all linked s…
Parsing JSON from SQL Server
I have a table with the following columns, one column being a JSON blob. I’m unclear how to parse the JSON blob as a series of columns alongside the other columns. I know there’s something called OPENJSON, but not sure how to apply it to this case. The json string example is below, from the DATA c…
How to make a separate column for each condition using one sql query?
I have a table with the data in the following format: It contains the number of seconds each user has watched tv on a specific day. One user can have more than one watching session per day. For example, user 1 has watched tv three times on Monday. What I need to do is to create a separate column for
How to generate INSERT Statements with Subqueries in Oracle SQL Developer?
I need to move some data from Environment A to Environment B. So far, so easy. But some of the columns have FK Constraints and unfortunately the lookup data is already on Environment B and has different PKs. Lucky me, there are other unique columns I could do a mapping on. Therefore I wonder if SQL Developer …
Take last hour and group it by 1 minute
I was wondering if you can help me write a query that should just SELECT count(*) but only include data from last hour and group it by minute. So I have a table that has a createdts so I have the date there. I just want to see how many entries I have in the last hour, but group COUNT(*)
postgresql search column with integer[] with another array
I am trying to figure out a way that I can search a column containing an array of integers. For example, column a -> [1,2]. Using another array, ex: b -> [1,2,3] If all the values in a are in the array b, I want to return that result. I’ve tried using unnest and = ANY but to no avail.
Count multiple string and put the count of each string into column
I’m really a newbie in sql and bigquery. In this case I use bigquery-public-data.san_francisco_bikeshare.bikeshare_trips dataset (image 1 and 2). I want to count total of subscirber_type, ‘Subscriber’ and ‘Customer’, and their average duration_sec. So the column I want to make it…
SQL copying data with new foreign keys
I have a very tricky problem here. I have two tables. One is dependent on another by foreign key. Table 1 Table1ID Instance ID ModifiedBy 1 1 yevhen 2 1 yevhen 3 1 yevhen Table 2 ID Instance ID Table1ID 1 1 1 2 1 2 3 1 3 These are taken as datasets for something named “Scenario”. When somebody
Merge two row in one from different table
Below is simple example, I need in one row display value from different table. eg. need resaults as: Answer With sample data you posted, that’s join with coalesce.