I have this two query’s: I would like to show them in the tabular form as shown on the image below… 1 Desired result: Answer One of the options: One more way: This will work on SQLServer:
Tag: sql-server
Is branch pruning possible for recursive cte query
This is inspired by question Retrieve a list of lists in one SQL statement – I have come up with a solution, but I have doubts on its efficiency. To restate the problem: we have 2 Tables: Person and …
Random Time Distribution using SQL Server
I have a table in SQL Server (current data) Total Over Time of EmpId 1001 in January-2020 is 3hrs 24 min I want to distribute/add 3hrs 24min randomly between 1-jan-20 to 31-jan-20 to OutTime column …
What is this TSQL V() method/syntax/function?
I have just come across this syntax for defining an inline view… SELECT myAlias, myAlias1 FROM ( SELECT myCol, myCol1 FROM myTable ) V( myAlias, myAlias1) I can see what the V is doing, but what …
CTE or not for counting tree id’s referenced in secondary table
I have a simple tree table in which I reference types, sub-types (no level limit), brands and models for a simple inventory application. data structure is as shown below: (nodeIsModel is a boolean …
How to delete duplicate rows in a table based on what is supposed to be a unique column referring to a table in another database
In Table A PersonID refers to Table B another database and is supposed to be unique here. For reasons, it isn’t and a stored procedure that uses PersonID and FacilityID as input parameters is returning multiple rows for the instances of that duplicate PersonID. I would like to remove the top record in T…
Having trouble with adding one property to an object inside a list after assigning the rest of them
I’ve been having trouble (again) with some object-oriented code. Currently I have a piece of code that populates a list with objects and its properties that goes like this: What I need is to add the baseID property to each of the objects. This is what I have currently: I want this loop to take the value…
How can I generate JSON results in older SQL Server versions?
I have a table which contains data similar to this. I want to get ItemNames in JSON format as I have mentioned in the expected output below. Expected output: Note: I know in latest SQL Server, there are functions FOR JSON to convert to JSON format but it won’t work in older SQL versions. Therefore, I am…
Getting Msg 8623, Level 16, State 1, Line 1 error on a simple select query on one table
How to optimize a simple query that search on one table for IDes that are not a part of a set. I created the following query Be aware that the list includes bit more than 35000 rows. I get the following database error Msg 8623, Level 16, State 1, Line 1 The query processor ran out of internal resources and
Python SQL script inserts only the last row of the dataframe to SQL Server [pyodbc]
I am trying to import a pandas dataframe to Microsoft SQL Server. Even though, the command is correct and executed successfully, when I select the first 1000 rows of the table in SQL server only the …