I have a database where we would store Yes/No questions as bits. However, as the project is going to a different path, we need to change all bit data types to int with default value NULL. Trying the following query, I get object is dependent of column error. To fix this I try doing: However, the fact that the…
Tag: sql-server
Insert query: Column name or number of supplied values does not match table definition
This is a table that auto increments the ID, takes the time input, and sets the default of the total column as zero and the date column with getdate() So the only value that I have to insert is the time. However on insertion SQL Server gives me this error: Column name or number of supplied values does not mat…
DROP Constraint without knowing the name
I know there are a lot of references out there for what I am going to mention. I am rather sharing something I feel should be very useful and consolidating in one place – Q&A-style. I have struggled earlier finding this for different constraints on different time. The following constraints are commo…
Can I have SQL incrementally count XML elements while parsing?
So this is my first foray into parsing XML, and I’m trying to figure out how to get this to work how I want. Given the following XML format: I want it to put it in a SQL table as the following: Basically, imagine a grid, and each “TileRow” starts a new Row in that grid. Each “TileValue…
How to select a foreign key after narrowing down via Group By and Having in a subquery
I’ve got a unique problem. I’m querying a replicated database table cost_plan_breakdown, and the replication is known to have some duplicates due to issues with deleting records. I’m not the Admin so I’m trying to sidestep these duplicates as efficiently as possible. The table looks li…
Can we concat as well as replace string values in SQL Server (SSMS)?
I executed below query, but I got result as null values. Instead I want my output to be: Answer The more recent versions of SQL Server support concat_ws(): In older versions, you can use:
Search using LIKE operator with multiple dynamic values accepting both full and partial text match
Is there any way to do multiple term search in a column using like operator dynamically in SQL Server? Like below For example: input values “goog; micro; amazon;” (input value should auto split by delimiter ‘;’ and check the text exist in the table) means that Search term ‘goog&#…
Apply SUM( where date between date1 and date2)
My table is currently looking like this: I’m trying to sum values of ‘ON_Prepaid’ over the course of 7 days, let’s say from ‘2020-01-01’ to ‘2020-01-07’. Here is what I’ve tried Things should be fine if the dates are continuous; however, there are some mis…
SQL Check if the User has IN and Out
I need help getting the User which has an ‘IN’ and ‘Out’ in Column isIN. If the user has an IN and OUT do not select them in the list. I need to select the user who has only had an IN. Please I need help. Thanks in advance. This is the table: Need to get the result like I
Include Column name in SQL Unpivot
I have an unnormalized data set I need to normalize for reporting purposes. I successfully unpivoted the data using this: How can unpivot column headers Question1, Question2, Question3, Question4 as a third column in my unpivot query, like this? Answer Your syntax looks like SQL Server. If so, just use a APPL…