Tag: tsql

TSQL : conditional query

I have the following table : | RoomID | OrderID | Occupancy | rn | +——–+———+———–+—-+ | 01 | 101 | Vacant | 1 | | 01 | 102 | Occupied | 2 | | 01 | 103 | Occupied | 3 | | 01 | 104 | Vacant | 4 | | 02 | […]

SQL Server : string to rows with delimiter?

In SQL Server 2016 there is a function called STRING_SPLIT (Transact-SQL). Answer to my question would work fine with this function. Unfortunately we only have SQL Server 2012. SELECT email, [Special ftg], [Special OrgCode], [Special SalaryCode] FROM Employee How can I get this? email Special ftg Special OrgCode Special SalaryCode —————————————————————————————– test@gmail.com 4;200;210;220;250;275;1100;1101;1102 14000000000 1 […]

SQL: Check if entry exists in one table to another and remove duplicates

The same question actually as this one: sql: check if entry in table A exists in table B Here are my tables, Employee and User Employee User EmpNo EmpNo PositionCode I just want to check if the EmpNo in table:Employee already exists in table:User here’s the code that ive used: SELECT Employee.EmpNo, PositionCode FROM Employee […]

sp_send_dbmail sending HTML instead of HTML formatted email

To begin, I have reviewed the following question and incorporated it into my problem: HTML Format in sp_send_dbmail I am trying to dynamically build an email that would send to separate people if needed. It appears that my string builds just find and sends the email. The issue is that the email just appears as […]

how to add attribute for all subnodes of the xml in sql

I have a xml like below in a variable @xml <ContentTemplate> <Tab Title=”Lesson”> <Section Title=”Lesson Opening” /> <Section Title=”Lesson/Activity” /> </Tab> <Tab Title=”Wrap Up and Assessment”> <Section Title=”Lesson Closing” /> <Section Title=”Tracking Progress/Daily Assessment” /> </Tab> <Tab Title=”Differentiated Instruction”> <Section Title=”Strategies – Keyword” /> <Section Title=”Strategies – Text” /> <Section Title=”Resources” /> <Section Title=”Acceleration/Enrichment” /> […]

Saving Dynamic query result into variable

I have a variable (@a) with query: select Field1 as [TD],Field2 as [TD] from MLSFeed For XML raw(‘tr’), Elements How to save xml result (dynamic query) into another variable (@b)? I tried to create Temporary table and insert there result of DynamicSQL, but that doesn’t allow to enter xml result into table.

Best way to concat 1 to n values into single field from two tables

T-SQL Imagine two tables looking like this: Table: students ============================== | TeacherID | SName | | 1 | Thompson | | 1 | Nickles | | 2 | Cree | ============================== Table: teacher ==================================================== | TeacherID | TName | + many other fields | | 1 | Pipers | | | 2 | Slinger | […]

How to Update Executed table result into the same table?

I have created a table tbl_Dist with Column names District and DistCode, there were many duplicate values in the District table so i have removed all the duplicates value using this statement: select distinct District from tbl_Dist; its done, but i am not getting how to update the results of the above executed query to […]

T-SQL Fixing Row Order / Sequence

Overview: I have a page in my application that allows a user to set the priority of a list of records. These records are essentially tasks or goals that the team will need to complete. The order they are given is determined by the user dragging and dropping them into place. Once the user is […]

Stored procedure get data from query SET-based

Hello I am making a stored procedure and I am having problem outputting the code with a print because later on i need to write the variables to a temp database. This is the code: SELECT fmsTotalAmount + fmsAirTotalAmount + fmsProjectsTotalAmount TotalAmount, fmsRelationAmount + fmsAirRelationAmount + fmsProjectsRelationAmount TotalRelationAmount FROM ( SELECT SUM( CASE WHEN fms1.currency […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.