I’ve been stuck on how to write a particular query for the following question: How many employees are in how many businesses? My end result should look like this: EmployeeId Count BusinessId Count 1 23473423 2 56245764 3 834456 So there are 23473423 businesses that have 1 employee, 23473423 businesses t…
Tag: sql-server
Delete duplicate records on SQL Server
I have a table with duplicate records, where I’ve already created a script to summarize the duplicate records with the original ones, but I’m not able to delete the duplicate records. I’m trying this way: The idea was to take the last record of each COD_PLANO_PAGAMENTO and delete it, but thi…
How can I print the connected database information (such as db name) in PHP?
I am trying to execute an SQL query in PHP but even though the DB has been connected it returns 0 rows, but when I try to execute the same query in MSSQL it returns a row. I have tried the one below but it returns just Boolean false: Here is how I execute my query: I just want to
Insert multiple values from ListBox with stored procedure
I have two related tables, the first called postaDip (IDpostaDip, CODdip, CODposta, from) and the second called cassPosta (IDposta, Desc). I am trying to insert multiple rows into the postaDip table through a listbox that loads the table cassPosta. For example I would like to insert multiple lines of as many …
Pagination with grouping
I have a table with over 800K records. This table needs to be “grouped by” a certain column. An example would be: However, with pagination, things get complicated. I want to show 50 rows for every group. This means that if I have the following data: Id Color Name 1 Red Paper #1 2 Red Paper #2 3 Re…
Dynamic LIKE in WHERE clause in T-SQL with STUFF and FOR XML Path
What I’m trying to do is search a text column for anything LIKE anything in a list of values. The table with the text column is very large, so I can’t join on LIKE ‘%’ + valuename + ‘%’ (the list of values temp table is not terribly large). I thought I would try to do it th…
How can I select multiple columns while using “when” statement
Here is the syntax I wanted to use for “loan amount” column. I am creating a data set on ssas for reporting. But I am having a problem adding “loan id” column based on the syntax since I need both “loan amount” and “loan ID” in the same data set. How can integra…
How to split comma delimited data from one column into multiple rows
I’m trying to write a query that will have a column show a specific value depending on another comma delimited column. The codes are meant to denote Regular time/overtime/doubletime/ etc. and they come from the previously mentioned comma delimited column. In the original view, there are columns for each…
Pivoting but handling Column Names
I’m currently getting a result-set back as follows: What I’m trying to do is get the results to appear as follows: I’ve put together the following query, and I was curious as to whether someone had a better way of doing it: Here’s the sample data: Answer You can use conditional aggrega…
Insert columns into new table where one of the columns doesn’t allow null values
I am attempting to insert 50 rows at a time from one table into another, however I am unable to bypass the ‘Not Null’ column in the table I am attempting to INSERT INTO. Please note I am not able to alter that column so it accepts nulls. I have 2 tables (table 1 and table 2). I am inserting