I have 2 tables: I want to get average of points for each student and each course and sort the result by Student ID and Course in order (Mathematics,Physics and Chemistry). The output should be the following: For each Student the course order should be (Math,Phys, Chem) and also it should be ordered by Studen…
Tag: tsql
How to order columns results in another column (SQL Server)
I have this table The key columns in my table are RowCnt , Lvl , a_TargetID , b_TargetID I want to put the name of the column as the order of their value in the a_1st a_2nd a_3rd a_4th so the final results will be like this I can do this but it seems very wrong I wonder if someone
How to copy parent and child data from one database to another while assigning the newly created parent ids in the foreign key column of child tables?
Suppose, I have a table tblClasses and tblStudents Now each class have multiple student. tblStudents now, I have another server having exact same db and tables and I am copying data from server 1 to server 2 from same tables using the Select and Insert e.g. and for tblStudents now this is ok but the real prob…
Searching through different tables?
I have several tables with a Sequence as the primary key. They are unrelated at all, there are no foreign keys or anything, or anything that could link one to another, so join won’t work. Union won’t work either because they are basically different tables. I know that you could assign “null&…
Combining data into one table cell
There is a query that returns a temporary table. TSQL Query: Result: Random sampling of output values Is it possible to group data from the last column, thereby reducing the number of lines in the output. That is, I need it to be like this: Desired result UPD: SQL Server 2016. STRING_AGG is missing Answer Gro…
Does declaring variables inside an exec improve SQL query plans?
I have a bunch of stored procedures that I’m looking to optimize and want to know if changing from having the variables injected into the WHERE condition to having them inject into a variable declaration will help The current statements look like this where the ids are passed into the stored procedure a…
Power function in T SQL
The following returns .81 as expected: But this does not. Its return value is .8: To have it return .81, I have to cast the input: Why is a cast needed here? What am I missing? Answer The power function return type is dictated by the data type of the first parameter. Here, you’re giving it a decimal(1,1…
Calculate the time period a certain field is overlapping in SQL Server
I am currently trying to figure out the overlapping time period between records. I already have a query that indicates if it is overlapping another field with ‘yes’ and ‘no’. But I need to have the exact duration of the overlapping time period when the field has ‘yes’ as in…
Adding custom value to SELECT
I have this query: I want to use the record_dt of the second query as a replacement of the record_dt in the first query. I want to do that by linking the values on the part_id since they both have it. First query is giving a list of record_id that has part_id, the second query is taking the latest record_dt
How can I delete record based on date?
I have a set of data with DateTime type in SQL. I want to delete whatever data belong to a specific date for ex: 2021-11-02 21:07:52.663. if I have value like above I want to delete records using just date. 2021-11-02. Answer This will compare the date part of your datetime to the desired date: