Using Newly Created Column For Doing Further Calculations In SAME Query

Apr 12, 2015

I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1

First 3 Columns are available within the Original Table_1

The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.

View 4 Replies


ADVERTISEMENT

SQL Server 2014 :: Using Newly Created Column For Doing Further Calculations In SAME Query

Apr 12, 2015

I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.

View 8 Replies View Related

Using Newly Created Column For Doing Further Calculation

Apr 12, 2015

I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1

First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me.And now I have to do various calculations that involve making use of both these type of columns.

View 2 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

Jul 25, 2015

I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.

The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

Below is the schema of the three tables

I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.

DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'

[code].....

View 2 Replies View Related

Getting Newly Created Id Number(asap)

Nov 8, 2007

 hello, i have a problem in my sql. im using a stored procedure and i want to get the newly created id number to be used to insert in the other table.it works like:insert into table() values()select @id = (newly created id number)  from tableinsert into table1() values(@id) something like that.    

View 2 Replies View Related

Execute A Dts Package From A Newly Created JOB

Feb 17, 2000

I am trying to execute three dts packages from a Job that I created. When creating the steps I am setting the Type To: Operating System Command(CmdExec).

The Command I am using is: dtsrun /S servername /E /dtsPackageName

For some reason whenever I try to run thes jobs it fails. Within this Job I have three steps. For each step I am using the same job but with a different package name. I have a trusted connection so my command should work just fine. What am I doing wrong. Can some please explain this process.

Thanks

View 1 Replies View Related

What Happened To My Newly Created SQLCLR ??

Feb 21, 2006

Hi

I had DTS the Northwind sample database from SqlServer 2000 to 2005. It's went ok and no errors. Then, I created a SP named upGetCustomer, bascially it queries the Customers table and list some of it's fields and order by CustomerId,CustomerName, Country decrementally. SP is so simple and has no errors.

Then, I created a SqlServer project in VS2005 using C#. Add store procedure class as below,
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Customers(string customerid)
{
using (SqlConnection sqlConn =
new SqlConnection("context connection=true"))
{
sqlConn.Open();
SqlPipe sqlPipe = SqlContext.Pipe;
SqlParameter param = new SqlParameter("@custId", SqlDbType.VarChar, 5);
param.Value = customerid;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "upGetCustomer";
sqlCmd.Parameters.Add(param);
SqlDataReader rdr = sqlCmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);
}
}

I had taken method of created SQLCLR from source in Microsoft website, but coding is mine, and hopes it is correct. I used SqlConnection string as 'context connection=true' which I still not quite sure what does it means, hopes it mean current connection I am using on my Windows authentication.

The project did compiled & deployed OK on the VS2005 side.

The problem is that when I tried to locate the assembly on the Sql Server 2005, but can't find it anywhere on Sql Server.

I did try complied and deloyed again, it keeps saying there is an error in deployment as customers assembly is already exist on the database. I then, tried to remove this assembly on the database using SQL script, drop assembly customers in the Northwind database but got error saying it does not exist. So where is it???

Please help...

Thank you









View 1 Replies View Related

T-SQL (SS2K8) :: Calculate On Newly Created Columns

Aug 2, 2014

I have a table that I have created a table and desire to do some basic math by adding a few new columns. The problem is that i cant get this to work without create many new select statements. The new columns that I wish to add refer to other newly created columns. Is there a way I can do this with CTW or subqueries? Unless it is a best practice to chain out the logic for the newly created columns

I have an example from AdventureWorksDW since the data is very accessible. I can safely create EMP_TENURE and PTO_REMAINING is this select statement. I would then need to create a new select statement to define 'BONUS' and then another select statement to define 'NEW_COL1' and so on.

Im still pretty new at SQL and am trying to learn how to complete such a task using subqueries or CTE.

SELECT
NAME = [LastName] + ',' + [FirstName]
,HireDate
,Title
,DepartmentName
,BaseRate
,VacationHours
,SickLeaveHours

[Code] ....

View 1 Replies View Related

Template Table Used In My Newly Created Database

Jun 18, 2008

I created a database called sports and then created a table which I'm going to use as a template for other tables within the same database.

What is the proper way to structure and implement copying this template table?

Should I store the template table under the system tables folder?

How do I copy the template table and create a new table from this template?

Thanks you
Goldmember

View 3 Replies View Related

Login Failed On A Newly Created Database

Mar 10, 2007

Hi!
I have a piece of code that connects to the master database and uses the simple 'CREATE DATABASE MYDATABASE' statement to create a new database on an sql server 2005 express instance.

I then close that connection and try to open a new connection using my newly created database. I use integrated authentication in both cases. However, I get the following error: Cannot open database "MYDATABASE" requested by the login. The login failed.Login failed for user 'DOMAINNAMEusername'.System.Data.SqlClient.SqlException: Cannot open database "MYDATABASE" requested by the login. The login failed.

I don't get this error all the time. I tried to run my code many times and it some times work. But in some cases it doesn't. The database is always created but some times I cannot connect to it. But again, if I wait a little and try again with the same database, the connection is made.

I know that my connections are opened and closed normally and they don't remain open. So I don't believe I reach the maximum number of available connections.

I also sometimes get this exception : A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.

Any clue?

View 5 Replies View Related

How To Use Newly Created Database In Visual Studio 2005

Mar 23, 2008

I created a new database in SQL Server 2000, using the command
"CREATE DATABASE ContactManager ON PRIMARY(NAME = ContactManager,FILENAME ='C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf',SIZE = 5MB,MAXSIZE = 20MB,FILEGROWTH = 5MB)LOG ON(NAME = ContactManager,FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.ldf',SIZE = 2MB,MAXSIZE = 10MB,FILEGROWTH = 2MB)Go"
Databse was successfully created and I created two tables also in it. Now when i try to create a new connection in Visual Web Developer 2005, I get the error message :
"Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf" failed with the operating system error 5 (Acess is denied.).
Cannot attatch the file "C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf" as 'ContactManager'."
 
What else do I need to do use the database?

View 7 Replies View Related

SQL 2012 :: Generate Triggers For Newly Created Tables

Sep 11, 2014

I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?

View 1 Replies View Related

SQL 2012 :: Why Are Databases Showing Up In Newly Created Instance

Sep 18, 2015

Why are the user databases that were created in the MSSQLSERVER default instance showing up in the newly created ALPHAONE instance? I'm successfully logging into the alphaone database as it shows as "DASAlphaOne,1xxxPport) at the top of the treeview in ssms. I'm logging in as sa and can edit anything.

The issue here is that all the user databases are shown and can even be edited. I created this instance in an effort to hide databases from whoever is not supposed to see.I was expecting a clean instance with only the system databases..Is there something that can be set to keep each instance's databases private into itself?

View 8 Replies View Related

Newly Created Witness Server Only Connects To One Of The Partners?

Apr 2, 2007

We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.



See screenshot here





Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?

View 9 Replies View Related

Use Newly Created Table In Data Flow Task

Jul 20, 2006

Hello dear Forum,

I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.

As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...

VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...

If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:

Package Validation Error: "Invalid object name 'myToBeCreatedTable'".

Can't i create tables on the fly which i use then in my dataflow tasks?

Kind regards,

Wolfgang

Hello dear Forum,

I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.

As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...

VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...

If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:

Package Validation Error: "Invalid object name 'myToBeCreatedTable'".

Can't i create tables on the fly which i use then in my dataflow tasks?

Kind regards,

Wolfgang

View 2 Replies View Related

Using Newly Created Table In Data Flow Task

Jul 20, 2006

Hello dear Forum,

I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.

As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...

VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...

If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:

Package Validation Error: "Invalid object name 'myToBeCreatedTable'".

Can't i create tables on the fly which i use then in my dataflow tasks?

Kind regards,

Wolfgang

View 1 Replies View Related

SQL Server 2008 :: Newly Created Index And Execution Plan

Jun 17, 2015

I run a query

select col1, col2, col3, col4
from Table
where col2=5
order by col1

I have a primary key on the column.The execution plan showing the clustered index scan cost 30% & sort cost 70%..When I run the query I got missing index hint on col2 with 95% impact.So I created the non clustered index on col2.The total executed time decreased by around 80ms but I didn't see any Index name that is using in the execution plan.After creating the index also I am seeing same execution plan

The execution plan showing the clustered index scan cost 30% & sort cost 70% but I can see the total time is reducing & Logical reads on that table is reducing.I am sure that index is useful but why there is no change in the execution plan?

View 7 Replies View Related

SQL Server 2014 :: Moving Old Data Out Into Newly Created Historical DB

Sep 29, 2015

I am getting ready to start a project where I am charged with moving out old data from production into a newly created historical DB. We have about 8 tables that are internal audit tables, that are big and full of old data. These tables are barely used and are taking up way too much space and time for maintenance.

I would like to create a way (SSIS?) to look at the date field in each of the 8 tables and copy out anything older than two years into my newly created history DB. Then deleting the older records from the source DB.

I don't know if SSIS is the best method to use. If it is, what containers to use to move over data, then how to do delete from source?

Can I do the mass deletes on my audit source tables without impacting performance/indexes/fragmentation?

View 5 Replies View Related

SQL Security :: How To Create Database Specifications On Newly Created Database Automatically For Audit

Jul 15, 2015

I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.

View 6 Replies View Related

SQL Security :: How To Copy Permission Of A User To Newly Created User

Oct 24, 2015

How can i assign permissions to a newly created users as of an existing user?

View 3 Replies View Related

Transact SQL :: Get Only Newly Inserted Rows By Datetime Column?

Aug 17, 2015

I have a TableA where data get inserted from Excel(IMPORT/EXPORT wizard)

TableA;
ID(identity)   Date (NOT NULL Defaulyt Getdate() )                          
 Name            Phone
1                   2014-06-17 17:28:21.190          
Nick              12345678910
2                  2014-05-17 17:28:21.190        
 Stan              00045678910
3                  2015-08-17 17:28:21.190        
 Kim                 11111678910
4                  2015-08-17 17:28:21.190          
Tom                NUll 

 3,4 are the rows i have inserted now , you can see by date, likewise i have 100,000 rows(old and new combination) and now the data from excel to TableA can be imported/exported daily , hourly, weekly basis.

Now i want to find out only the rows which are imported to tableA today, or hours back, or yesterday .....

 the reason is , 

Step1:get data from excel and import to tableA( this is a manual Step) and i know when the data is inserted  with exact date and time.

Step2: get newly inserted rows from TableA and pass them as Parameters in Stored Procedure.( i may run step 2 after 1 hour, or after  1 day or after 1 week ,but i want only rows that are inserted ) 

I tried with using where Datecreated, but did work.

View 4 Replies View Related

Sql Column Calculations?

Jun 5, 2006

Timespan = 30 days
Start Date = January 1st
Last Processed Day = NULL
Next Cycle Day = IF(Last Processed Day IS NULL)                                 Start Date + TimeSpan                             ELSE                                  Last Processed Day + TimeSpan
Is it possible to setup a column to do this from sql?

View 1 Replies View Related

Column Calculations

Jul 23, 2005

Hi,I have a select query that returns three integer fields from a table thevalues range from 0 to 5. On each row I would like to calculate the averagevalue in the three fields however, the difficulty is that only the rowswhere the value is greater than 0 should be included in the calculation.To make this clearer please consider the following example:Col1 Col2 Col2 Average Calculation1 2 3 2 (Col1+Col2+Col3)/32 2 0 2 (Col1+Col2+Col3)/2The first row should be divided by 3 because each of the three columns has avalue greater than 0, however the second row should be divided by 2 becauseonly two of the value are greater than 0.Is this possible?Also is is possible to pass the results of one calculation into anothercalculation.Thanks in advance,Steve

View 3 Replies View Related

Computed Column Calculations

Jul 23, 2005

If i want to split a computed column into two or more columns based onthe the length (its a varchar) of the computed column, how often willsql server determine what the computed column is? roughly along thelines ofselect comp_col,'comp_col 2'=case when datalength(comp_col)<=100 then comp_colelse left(comp_col,99) + '~' end,datalength(comp_col)from aTableAs you can see, in this scenario we use the computed coulumn,comp_col, in a few places, so does SQL server need to calculate thiseach time? I'm playing with this on the basis that it does and thustrying to shift the computed column out to a variable and thenmanipulte and return from their, but that has its own problems whenyou throw in additional parameters (trying to join table udf's) so ifSQL server is smart enough to not calculate the column each time Iwould save a lot of hassle?Cheers Dave

View 9 Replies View Related

Using Alias Column Names For Calculations

Oct 3, 2007

Hi,
I want to have a query where in i can use alias column names in the same query.
like eg
select 1 as 'a', 2 as 'b', a+b as 'c'

note that this query is getting big and is using sub queries.


Kindly help.

Thanks

View 5 Replies View Related

Create A Column Based On A Value In Created Column

Jun 20, 2014

I am trying to add a column to query based on the value of another column in the query.

I first tried creating a calculated field in SSRS 2008 with this statement:

=IIF(Fields!ChargeableFlag.Value=1,Fields!Negamt.Value,0)

The report runs but I get a "#ERROR" when I place the field on the report.

I next tried creating a new column with the SQL statement:

SELECT Project.ProjectCode AS PC, Project.StatusCode AS SC, Time.StandardHours AS Hours,
Time.StandardChargeAmt AS StdAmt, Time.TaskUID as UID,
Time.StandardChargeRate as Rate, ChargeableFlag, 'Bill' =

Case
When TaskRule.ChargeableFlag = 0 Then 'Non-Bill'
When TaskRule.ChargeableFlag = 1 Then 'Billable'

[Code] .....

This query, less the case statement for BLAmt creates the dataset for the SSRS. Adding the Case statement for the BLAmt produces the error: "Invalid column name 'Negamt'."

View 2 Replies View Related

Transact SQL :: Query To Avoid IF And CASE And Calculations Carried Out Once Only To Speed Up With Common Comparing Columns

Oct 22, 2015

Got a query taking too much time because of lack of cross columns MAX/MIN functions. Consider a similar example where a View is required to reflect distribution of Water among different towns each having four different levels of distribution reservoir tanks of different sizes:In this case the basic table has columns like:

PurchaseDate
TownName
QuantityPurchased
Tank1_Size
Tank2_Size
Tank3_Size
Tank4_Size

Now suppose I need a query to distribute QuantityPurchased in the Four additional Columns computed on the basis depending on the sizes declared in the last four fields,in the same order of preference.For example: I have to use IIFs to check: whether the quantity purchased is less than Tank_A if yes then Qty Purchased otherwise Tank_A_Size itself for Tank_A_Filled

then again IIF but this time to check:

Whether the quantity purchased less Tank_A_Filled (Which again needs to be calculated as above) is less than Tank_B if yes then Tank_A_Filled (Which again needs to be calculated as above) otherwise Tank_B_Size itself for Tank_B_Filled

View 2 Replies View Related

Mutiple Column Group Subtotals, Custom Subtotals Expressions/Calculations

Jul 9, 2007




































Some DB Field ID
X
2007
2008

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
A
X-A
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
B
X- A -B

1
500
10
20
0
0
0
0
0
0
0
20
50
0
100
400
25
10
10
0
0
5
0
25
15
10
10
20
130
270

2
750
10
10
10
20
20
10
10
20
10
10
10
10
150
600
20
20
20
20
30
30
10
10
10
30
30
30
260
340

3
600

































All,



I am trying to achieve something as above. Basically, the Months subtotals are represented by A and B. Then (X-A) and (X-A-B) are also the subtotals at the same group level as A and B but don't simply display the total for respective years 2007 and 2008, instead those are remaning totals from X. In order to calculate the remaining totals however, one need to consider the subtotal in previous group. For example, for 2007 its X-A, but for 2008 its X-A-B. I would like to know if this can be achieved using Matrix control. If so, what would be the steps?



Thanks.



View 5 Replies View Related

Sql Challenge : List Created From Column Data.

Apr 15, 2008

Lets say I have a table called MyData.

MyData has two columns, ID (int), and Value (varchar(x) )

Lets say the data in the table looks like this:

ID Value
- -----------
1 A
1 B
1 C
2 A
2 D
3 E
3 F
3 A
3 G

now, is there any way to construct a select statement from this data that will return a dataset that looks like this?

ID ALL_VALUES
- --------------------
1 A,B,C
2 A,D
3 A,E,F,G


In other words, is there a way to combine all the Value field values into one field for each ID?



View 8 Replies View Related

Possible To Avoid The FILLING Of GAP (created By Deleting A Record) In The Identity Column ?

Jun 18, 2007

Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy

View 2 Replies View Related

Power Pivot :: Relationship Cannot Be Created Because Each Column Contains Duplicate Values

Nov 15, 2011

I have 2 excel tables files.One table has info about sales by country, by model, by date...and the other table has units by date.Obviously, the common key is "date"....and by creating a relationship, I can add "units" to my combined resulting table.However, I can't create the relationship. Excel keeps telling me: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".

View 15 Replies View Related

Table , Column Created, Modified Date In Sql Server 2000 Or 2005

Oct 10, 2007



Hi

Any one please tell me is there any possible way to identify the table modified date.

I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates.
Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000.

I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date
can any one help

View 8 Replies View Related

Get New Database Created Then Running Script To Created Tables / Relationships

Jun 29, 2015

trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication. 

E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".

From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go   CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved