I have a table strcuture (golf score card) as follow: HoleID int CourseID int Par INT Distance INT LowIndex INT HighIndex INT Sequence INT (representing the sequence of the hole: 1, 2, 3, ... 18) This course has 18 holes. I want to pivoting into: Hole, 1, 2, 3, ... 18 Par, X, X, X, ... X Distance, Y, Y, Y, ... Y LowIndex...
I have a secondhand sale car database and four tables with relationships. Same column names have relationships. Tables are: Record: RecID Markname Model... Features: FeatureID Featurename Marks: MarkID Markname Carfeature: CarfeatureID RecID FeatureID Now, i want to delete a mark from Marks in c#. When i delete a mark, SQL must delete ...
I'm trying to insert some text data into a table in SQLServer 9. The text includes a single quote. How do I escape that? I tried using two single quotes, but it threw me some errors. eg. insert into my_table values('hi, my name''s tim.'); ...
Hi guys, I have two columns of data in a SQL 2005 DB Datetime Value '2009-10-29 10:00:00' ' 10.1' '2009-10-29 10:15:00' ' 10.2' '2009-10-29 10:30:00' ' 10.3' '2009-10-29 10:45:00' ' 10.4' I want to SELECT Value FROM [table] WHERE Datetime >= '2009-10-29 10:00:00' AND (Datetime NOT BETWEEN '2009-10-29 10:14:00' AND '20...
Hi everbody, I have the following table (the number of the references is variable): Id | FK_ID| Reference | ----------------------- 1 2100 GI2, GI32 2 2344 GI56 And I need the following result: Id | FK_ID| Reference | ----------------------- 1 2100 GI2 2 2100 GI32 3 2344 GI56 Is there any short way to tr...
Hi I need to develop a query that will count the total number of 'open' cases per month. I have a 'cases' table with an id and a name, and a 'state_changes' table with a datetime column, a caseid column and a state. How can I calculate the number of cases in each month that have a record with state 'open' in the past, but without a cor...
Is it possible to shorten a group by clause so that you do not have to repeat the fields mentioned in the select clause ad nauseam? For example: SELECT field1, field2, field3, field4 FROM table GROUP BY field1, field2, field3, field4 to: SELECT field1, field2, field3, field4 FROM table GROUP BY SELECT.* ...
I discovered(*) today that, depending on the server, my TSQL commands were case-sensitive, meaning that, when one table's column is named tableId, the following instruction might not succeed: SELECT TableId FROM myTable Depending on the column's collation. SQL_Latin1_blablabla seems not to be case-sensitive, when Latin1_blablabla is. ...
I have 2 queries in MS SQL that return a number of results using the COUNT function. I can run the the first query and get the first result and then run the other one to get the other result, subtract them and find the results; however is there a way to combine all 3 functions and get 1 overall result As in: run sql1 run sql2 run SQL3...
Here's my scenario: Let's say I have a stored procedure in which I need to call another stored procedure on a set of specific ids; is there a way to do this? i.e. instead of needing to do this: exec p_MyInnerProcedure 4 exec p_MyInnerProcedure 7 exec p_MyInnerProcedure 12 exec p_MyInnerProcedure 22 exec p_MyInnerProcedure 19 Doing s...
I have this data as varchar '00072330'. How do I convert it to a decimal that looks like '723.30' in SQL Server 2008? ...
Are there any differences between these two covered indexes? A composite index with both FriendID and UserID A index with FriendID and UserID as an included column Separate MS SQL Server related point. If the answer to the above question is 'No difference', is it me or does the Database Engine Tuning Advisor (DTA) always go crazy on...
In SQL Server 2005 I have a table with data that looks something like this: WTN------------Date 555-111-1212 2009-01-01 555-111-1212 2009-01-02 555-111-1212 2009-01-03 555-111-1212 2009-01-15 555-111-1212 2009-01-16 212-999-5555 2009-01-01 212-999-5555 2009-01-10 212-999-5555 2009-01-11 From this I would like...
I have an existing, fairly lengthy, SQL query. I would like to select records which have a distinct mt.ID. I've tried inserting "SELECT DISTINCT" in various places without any success. Could anyone tell me where it should go? Thanks very much. SELECT * FROM (select ROW_NUMBER() OVER(ORDER BY " + orderField + @") as RowNum, ...
i have three tables (SQL Server) Month - month_id, month name, .... Award - award_id, award name, .... Nomination - fk_award_id, fk_month_id, name, address,... I need to count the number of different types of awards awarded per month while returning 0 in cases where nobody is awarded for ex. the results should look like A...
Ok I just realized that from my previous question: http://stackoverflow.com/questions/1590370/update-main-table-based-on-rows-from-a-secondary-table That was how to update the main table with the count using the secondary table, but since this query will be in a sql job, how can I delete the rows I just counted, and making sure I don't ...
I have a varchar(100) field that contains both letters and numbers. The values are usually in the form of car 1, car 10, car 100, car 20. But the values can have any word preceding the number. Is there any way to sort these values numerically so that car 2 will come before car 10? Thanks. ...
I have a table with 1 column of varchar values. I am looking for a way to concatenate those values into a single value without a loop, if possible. If a loop is the most efficient way of going about this, then I'll go that way but figured I'd ask for other options before defaulting to that method. I'd also like to keep this inside of ...
My content table looks like (contentID, title, created). I need to get all content that was created more than 3 weeks ago. Sql server database, created is datetime type. ...
Hi i am using the following dynamic sql: declare @cmd nvarchar(4000) set @cmd= 'select ''exec msdb.dbo.sp_update_job @job_id='''''' + convert(nvarchar(255), job_id)+ '''''', @owner_login_name=''''sa'''';'' from msdb..sysjobs' exec sp_executesql @cmd but all it is doing is printing as exec ....... I want to execute the results ...