I have 2 table variable @Data and @DateTable @Data conatains the following: Company Date Type Select Altec 12-1-2010 d 2 Altec 12-1-2010 a 2 Alect 12-3-2010 d 3 @DateTable contains the following: Company Date Type Select Altec 12-1-2010 a 0 Altec ...
Hello, Using SQL 2005, I am trying to run a query on a Orders table in my database. Each order can have multiple 'Comments'. I want to get a single order record with all the Comments put together in one field. Here is the psuedocode for what I'm trying to do: SELECT Orders.Name, Orders.Date, (SELECT Comment += Comment FROM OrderCo...
I'm trying to use the OUTPUT statement in a stored procedure in order to return the ID of a newly inserted row. The stored procedure is: CREATE PROCEDURE PROC_RESTORE_REQUEST_TO_QUEUE @cs_uri_stem varchar(900), @cs_uri_query varchar(2500), @date datetime, @time datetime, @queue_state smallint, @process_id int, ...
Hi, I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn't work. Query1: Works SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2 Query2: Does not Work. Error: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near ')'. SELECT * INTO [NEW_TABLE] FROM ( SELECT *...
Given the following rows of course,section,grade,count of grades within course section: course SECTION grade gradeCount ----------------------------------- 1301 001 C 3 1301 001 C+ 3 1301 001 C- 4 1301 001 D 5 1301 001 D+ 3 1301 001 D- 2 1301 001 F ...
So imagine that you have a table of Products (ID int, Name nvarchar(200)), and two other tables, ProductsCategories (ProductID int, CategoryID int) and InvoiceProducts (InvoiceID int, ProductID int). I need to write a query to produce a set of products that match a given set of invoice ids and category ids such that the list of produc...
I have a NVARCHAR(max) column in a table and a stored procedure that would update this column as well as any other column in the table using CASE switching: CREATE PROCEDURE updateTable @columnName sysname, @value nvarchar(max) AS UPDATE [dbo].[TestTable] SET BigNvarcharValue = CASE @columnName WHEN 'BigNvarcharValue...
Hi all, Trying to get a basic understanding of T-SQL here in SQL Server 2008. Suppose I have a table named "Issues" with columns such as: Priority User 1 Foo 1 Foo 2 Foo 5 Foo 4 Bar 5 Bar 1 Bar 1 Fuz and I wish to display a count of the Priority for each User, along with a break...
Is there a way in SQL sever that can write the output as follow: select events from mytable original output events -------- 123456 894531 985233 829292 920202 392939 299223 desired output '123456', '894531','985233','829292','920202','392939','299223' select '' + CustomerID + ',' from dbo.Customers customerid ALFKI, ANATR, ANT...
Possible Duplicate: concatenating string I posted a similar question earlier and asked about using SQL server to output the result with a single quote. Could someone edcuate me please? select ' ' + CustomerID + ',' from dbo.Customers customerid ------------ ALFKI, ANATR, ANTON, AROUT, BERGS, Would like to see the ...
For example, I need to change from to . I know PIVOT is for that, but it requires an aggregate function; and for my case, I donot need to aggregate only need column to row. You can use the following sample data: CREATE TABLE[StudentScores] ( [UserName] NVARCHAR(20), [Subject] NVARCHAR(30), [Score]FLOAT, ) GO INSERT INT...
Like the title says, in oracle you can issue the following command in SQL*Plus: SQL> select something from anothertable; #sql SQL> @{/home/me/somescript.sql}; #load sql from file and execute it SQL> do something else in script; #other sql Without having to file->open the sql script to load it to the UI. Is there an equ...
I have a table that simplified looks like this: create table Test ( ValidFrom date not null, ValidTo date not null, check (ValidTo > ValidFrom) ) I would like to write a trigger that prevents inserting values that overlap an existing date range. I've written a trigger that looks like this: create trigger Trigger_Test on Test for i...
SELECT au_lname, au_fname FROM authors WHERE au_lname = 'Green ' au_lname au_fname ---------------------------------------- -------------------- Green Marjorie SELECT au_lname, au_fname FROM authors WHERE au_lname LIKE 'Green ' au_lname ...
Simply, I have this SQL statment: EXEC xp_cmdshell 'tasklist' can we order or filter the results by using order by or where? Thanks, ...
Hi, I'm trying to do the following: IF NOT EXISTS ( SELECT * FROM [tbl_web_company] WHERE [name] = @name AND [address1] = @address1 AND [address2] = @address2 AND [city] = @city ...
I need to do the following modifications to a varchar(20) field: substitute accents with normal letters (like è to e) after (1) remove all the chars not in a..z for example 'aèàç=.32s df' must become 'aeacsdf' are there special stored functions to achieve this easily? UPDATE: please provide a T-SQL not CLR solution. This is ...
Hi, I'm having a problem with non-ASCII characters in a where clause Say for example a record in my table has : column_a Bom D� Street And I want to see if this will find the record: SELECT * FROM [tbl_test] where column_a = 'Bom D� Street' This always returns no records. Is there something you have to do to handle non-ASCII char...
My Issue is that the last query in this sproc is saying that I need to declare @N but I already have any ideas? DECLARE @t TABLE (Smpinstanceid UNIQUEIDENTIFIER) INSERT INTO @t (Smpinstanceid) SELECT t.SmpInstanceid FROM Tasks t WHERE t.Completed IS NOT NULL and t.SmpInstanceID is not null DECLARE @N TABLE (...
TSQL Function to calculate 30 WORKING days Date from a Specified Date (SQL Server 2005)? Input parameters would be Date and Number of Working Days. Output would be the Calculated Date. This would exclude Saturday, Sunday, Holidays and Day Holiday was observered. i.e. If the Holiday falls on a weekend but it is observed on the Friday ...