I need to insert multiple rows in a database table from a single string. Here is my string it will be comma-seperated values Current string: What I want is that batch 1 should be ignored or removed and remaining part should be added into the SQL Server database as a separate row for after each comma like this…
Tag: tsql
String Split String With multiple columns same separator
I have the following string ’06/30/2020;58044.373;.001;12/31/2020;58042.373;.003;06/30/2021;78044.373;.007′ I need to transform it into : col 1 col 2 col 3 —- —– –…
LEFT JOIN returning no results if a WHERE clause is added?
I’m trying to add the dbo.EmpHRSchedule.Type = ‘HOLIDAY’ entries to the Hours column only if they exist. To my understanding, a LEFT JOIN would only return entries if they match, and return NULL for non-matches. When I uncomment the AND ehrs.Type = ‘HOLIDAY’ line, no results are …
How to aggregate and Join or Union into flat Json object with arrays?
I have data that looks like: Customers Table CustomerContacts Table Here’s the result I’m looking for: What I have tried: I’m embarrassed to say I’m not even close: Answer Unfortunately, SQL Server jumped on the JSON wagon a bit late in the game (started built in support only in 2016 v…
Running total over duplicate column values and no other columns
I want to do running total but there is no unique column or id column to be used in over clause. there are only 2 columns, name which has duplicate values and the no on which I want to do running total in SQL Server 2017 . expected result: Any help? Answer The following query would generate the output you
Recursive CTE have performace issue, need suggestion to optimize query
I wanted to get TOP 5 records from log table where “Approve date” is changed like NULL to value and vice versa. Date value is doesn’t matter, but order matters. In this case I want first record and 5th record (Someone approved the data that’s why a value),then 7th record value is null someon…
Number based on condition
I’m trying to generate a number based on a condition. When there is yes in column ‘Stop’ in the partition of a Client ordered by Start_Date, the Dense Rank has to start over. So I tried several things but it’s stil not what I want. My table with current number and expected number The q…
NZ function in T-SQL where ValueIfNull is NOT specified
I am working on an MS Access to SQL Server Migration project and am currently in the process of converting a complex query into T-SQL from MS Access. Now I’m quite familiar with how Nz works in Access …
How can I convert an EAV schema to a normal schema using PIVOT?
I have a table called SourceTable, in that I have 4 fields. Properties_title field it has got 3 values (AAA,BBB,CCC) but can also have more. Depending on each of them, NumericValue field and Property_item_title field has a value.According to the table in the below, if Properties_title be AAA or CCC so Propert…
Query to “PIVOT” the results of SalesAmount & CumulativeMonthly for specific YEARS
I have a database table that captures every Sales Transaction: Transactions ( ID INT, TransactionDate DATETIME, SalesAmount MONEY ) I need to run a T-SQL query to produce a Sales …