Tag: tsql

Get Values between Each Comma in Seperate Row in SQL Server

sql sql-server sql-server-2014 string tsql

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 Table name

String Split String With multiple columns same separator

sql sql-server tsql

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?

sql sql-server tsql

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 returned at all. Why is this the case? Is there another way I

How to aggregate and Join or Union into flat Json object with arrays?

azure-sql-server for-json sql sql-server-2017 tsql

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 version), which means it’s JSON support is still not great

Running total over duplicate column values and no other columns

sql sql-server sqlite tsql

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

gaps-and-islands sql sql-server tsql window-functions

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 someone rejected it. I tried using recursive

Number based on condition

gaps-and-islands sql sql-server tsql

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 query I used so far: This

NZ function in T-SQL where ValueIfNull is NOT specified

database-migration ms-access sql sql-server tsql

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?

entity-attribute-value pivot sql sql-server tsql

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 Property_item_title it has value and if Properties_title be BBB