I have dynamically created a temp table #temp1 that create column dynamically based on a json string. The table will look like as below #temp1 ID Field FRUIT VEGE 1 Field1 A B 2 Field2 C D I would like to fill in the value column only in result table based on the #temp1 table, the field and type_id is
Tag: sql
Postgres not returning data on array_agg query as below
The problem arise when there are no data for books in specific library. Consider a following working scenario. Table name: library Table name: books Now when I do query like below: The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list…
SQL select data grouped by attr1 with different criteria on attr2 and attr3
I have table with data like this in Access: I need result grouped by attr1, count attr2 and count attr3 only if value is “Yes”: I would appreciate any help with sql. Thank you very much in advance. Answer The expression for the third column is the challenge here. Consider an IIf expression: IIf(m.…
Return decimal value in SQL query
Example of script ,sum(BREAKS)/3600 as Breaks but it comes back as a whole number, how can I get decimals?
Sql Server Global Blocked List and Item specific Allow List
This seems easy at first but after thinking about it for the day I’m stuck. Imagine you have a global block list for veggies. create table blocked_veggie(veggieid int primary key, veggiename varchar(…
How to write the sql in SQLite with this?
WITH cteCountDownlines AS ( –=== Count each occurrence of EmployeeID in the sort path SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT), NodeCount = COUNT(*) –Includes current …
LOOP/FOR statement on oracle script
I’m running an ansible playbook that runs an sqlplus script to an Oracle DB. Basically the script creates a CSV file with some server info. The query is pretty much autogenerated, so it will be difficult change it. The problem is that this query brings all the 5000 server and I need only 200. I want to …
How To Output results from a table where the data does not exist in the other table MYSQL
I have a table which places a client on a ‘grid’ with x and y coordinates from -10 to 10. I need to select the grid sections which there are no clients. I have tried using NOT EXIST but it does not show me the data I need. i.e if empty gird section was at -1-8 it would not select
WHERE IN performs much better with Table Type than with hardcoded values in SQL Server
I have 2 queries that are essentially the same (at least if I didn’t miss something). I was thinking that one cannot beat constants declared in the query itself, but apparently the first query is few times slower than the second one. It seems that SQL server is not smart enough to provide a good plan fo…
Consolidate SQL Union With JOIN
Let’s say I have 2 tables (bar_helper and bar). I have a query that retrieves records based on a JOIN between the tables and some condition. Essentially, my first result set is joined and I want my second one to be unconditional and just based on one table. This works fine. However, now I need to also g…