tsql

T-SQL 2005: Counting All Rows and Rows Meeting Criteria

Here's the scenario: I have a table with 3 columns: 'KeyColumn', 'SubKeyColumn' and 'BooleanColumn', where the first two are the primary keys of the table. For my query, I'd like to count the number of rows there are for any given value in 'KeyColumn', and I'd also like to know which ones have a value of true for 'BooleanColumn'. My i...

How to pivot rows into columns (custom pivoting).

I have a Sql Database table similar to the following: Day Period Subject Mon 1 Ch Mon 2 Ph Mon 3 Mth Mon 4 CS Mon 5 Lab1 Mon 6 Lab2 Mon 7 Lab3 Tue 1 Ph Tue 2 Ele Tue 3 Hu Tue 4 Ph Tue 5 En Tue 6 CS2 Tue...

How to concatenate multiple rows?

I have the following query which returns the salary of all employees. This work perfectly but I need to collect extra data that I will aggregate into one cell (see Result Set 2). How can I aggregate data into a comma separated list? A little bit like what Sum does, but I need a string in return. SELECT Employee.Id, SUM(Pay) as Salary F...

Rotate and summarize data

I have the following: declare @PrintJob TABLE ( PageNumber Int, Copies Int ) INSERT INTO @PrintJob(PageNumber,Copies) VALUES(1,100) INSERT INTO @PrintJob(PageNumber,Copies) VALUES(2,100) INSERT INTO @PrintJob(PageNumber,Copies) VALUES(3,100) INSERT INTO @PrintJob(PageNumber,Copies) VALUES(4,100) INSERT INTO @PrintJob(PageNumber,Cop...

How to reseed an an auto increment column in a SQLite database?

Is it possible to reseed an auto increment column in a SQLite database, and if so, how is this done? ie. The equivalent of DBCC CHECKIDENT ('MyTable', RESEED, 1) in SQL Server. ...

t-sql escape problem

i can not delete this lines from DB becouse of Unclosed quotation marks. How cn i escape it. I try with backslash but not working. When i try to delete Delete from dbo.Cities where name = 'Àrbatax'; i get (0 row(s) affected) even in DB 12 rows exist. Problem with unrecognized char À Delete from dbo.Cities where name = 'Ra's al Khaymah'...

Select records where sub records are from a list

Problem I am trying to write a stored procedure to select a list of NewsItem records where each NewsItem has all of the Categories that are in a list. If the list is empty then it should return all news items. NewsItem NewsItemCategories Category -------- ------------------ -------- NewsID NewsID C...

I am trying to get comfortable creating joins with T-sql 2008

I am trying to write this select statement. My select statement consists of a join that returns all of the first and last names of my fictional employees, their department names and I am trying to group the individuals by their respective departments. This is the code I wrote: select e.First_Name,e.Last_Name,Department_Name from EMPL...

updating a sum of one table based on input from another table

Hello, We've the following SQL that we use to calculate total costs. SELECT DailyProduction.CustomerId as CustomerId, SUM(Resource.CostPerUnit * DailyProduction.UnitsToStorage + Resource.CostPerUnit * DailyProduction.UnitsToMarket) AS TotalCost FROM dbo.hgm_ResourceTypes Resource JOIN dbo.hgm_ResourceDailyProduc...

The correct syntax for a T-SQL subquery and a possible join

What would be the correct syntax and join (if any) of a subquery that would return all of the employees first and last name from the employee’s table, and return their department name from the department table, but only those employees who more than the average salary for their department? Thanks for your answers ...

I am trying to correct this error code in T-SQL that states that I have an invalid column

This was my original question: “What would be the correct syntax and join (if any) of a subquery that would return all of the employees first and last name from the employee’s table, and return their department name from the department table, but only those employees who more than the average salary for their department? Thanks for your...

T-SQL Update trigger with multiple rows

Hi ! Consider a trigger after update on the table A. For every update the trigger should update all the records in table B. Then consider this query: UPDATE A SET X = Y Apparently there are many rows updated. After the update the trigger takes place. Now if the trigger would be using inserted table, and you would like to update the ...

Create a one row table with as many columns as there are rows in a table returned from a SELECT query.

I want a query to create a one row table with as many columns as there are rows in a table returned from a SELECT query., where the columns created have names which are values taken from some column of the SELECT query, and have values which are values taken from some other column of the SELECT query. e.g. If I have a table T1 with two...

TSQL Create trigger with transaction and try catch block

Hi ! i have some questions about a transaction in the trigger, for which I haven't found an answer yet. CREATE TRIGGER A_AI ON A AFTER INSERT AS BEGIN TRY --is the try block 1 transaction ? or do I have to begin the transaction? --BEGIN TRAN: may I start the transaction like this? -- SOME DANGEROUS OPERATIONS ...

displaying data from multiple tables

i have 3 table (SalesLog, Breakages, SalesReturn), I want to display data from these table like ProductName SalesQty BreakQty ReturnQty ABCD 1000 10 20 SalesLog Table CREATE TABLE [dbo].[SalesLog]( [SalesID] [int] IDENTITY(1,1) NOT NULL, [MemoNo] [int] NULL, [Product...

Multipart identifer could not be bound, correlated subquery

This is a contrived example using SQL Server 2008. I'm essentially storing a list of ids in an xml column in a table: temp (bigint id, xml ids) I want to join the table itself to the xml nodes. So far I have: select * from temp x join ( select x.id , ids.id.value('@value', 'bigint') zid from temp t cross apply ids....

cannt get a record by the row number

i get an error with this query select ID, ROW_NUMBER() OVER(ORDER BY ID) as num from T_TASK where ROW_NUMBER() = 5 and this one select ID, ROW_NUMBER() OVER(ORDER BY ID) as num from T_TASK where num = 4 whats wrong with the queries? ...

MSSQL Try Catch and Set Identity Insert in MySQL ?

Do you know how to rewrite these this query in MySQL ? I can't find Identity insert, I can't find any try catch, I don't understand it. CREATE TRIGGER T1 ON DB1.dbo.A AFTER INSERT AS BEGIN TRY SET IDENTITY_INSERT DB2.dbo.B ON INSERT INTO dbo.B(id, text) SELECT A.id,A.text FROM dbo.A INNER JOIN inserted I ON I.id = A.id SET ID...

Finding Caller of SQL Function

There's a SQL function that I'd like to remove from a SQL Server 2005 database, but first I'd like to make sure that there's no one calling it. I've used the "View Dependencies" feature to remove any reference to it from the database. However, there may be web applications or SSIS packages using it. My idea was to have the function in...

large insert in two tables. First table will feed second table with its generated Id

Hi all, One question about how to t-sql program the following query: Table 1 I insert 400.000 mobilephonenumbers in a table with two columns. The number to insert and identity id. Table 2 The second table is called SendList. It is a list with 3columns, a identity id, a List id, and a phonenumberid. Table 3 Is called ListInfo and co...