Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How To Combine Two Column In One Table Using SQL Statement ?


Could you write the simple SQL statement from 'Combine two column in one table '?





I try to use 'Union' which combine two column in two table . thx


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Combine Two Statement
Help please!

I have two statements getting data from two different tables.

1: select dataDate from table1,- gives ‘20080325’

2: select count (recordNumber) from table2,-- gives ‘4566’

Each table has one row but more than one column. The columns are not the same in each table.

Question. I need to combine two statements to give the outcome in one row as '200803254566'. '2008032' from first statement and '54566'from second statement. How do you combine them?
?

Josephine

View Replies !   View Related
Using A Variable To Specify Table Column In Sql Statement??
Hello everyone,
I'm still quite new ASP.net, Visual Web Developer 2008, and SQL. It has been a fun learning experience so far. Anyways, the site I am designing needs to allow its users to extensively search several different databases (MS SQL databases).  I have followed many of the tutorials and have found it rather easy to add table adapters, gridviews and other data features that use basic SQL Select statements. One of the major database tables contains several columns which I would like to include as a search parameters from a drop down list. I was wondering if there is any way I can write a select which will pass a variable to be used as a column name to the statement?  For example:
SELECT DATE, GAME, EXACT, @COLNAMEFROM HistoryWHERE @COLNAME = @SOMEVARIABLE
This obviously doesnt work, but thats the gist of what I want to do. Any suggestions? I need this to be simple as possible, Most everything I'm doing is done through the visual design mode. Im still slow to learn C# and apply it in the codebehind files unless I have very detailed step by step instuctions.
Thanks
Scott

View Replies !   View Related
Combine Fields And Text In Select Statement
Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
  SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST) 
 
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded

View Replies !   View Related
How Do I Get The Actual Name Of A Column Or Table In A Sql Select Statement?
Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

Robert

View Replies !   View Related
Combine Two Different Columns Into One Column
Hi Guys,
 
I have twotables(Employee and Borrower). In Employee table have EMPID and Borrower table have BorrowerID. I want to comebine these two columns into one column as EMPID  in Employee table. Can  any one help?
Thanks

View Replies !   View Related
Combine 2 Column Into 1 In Display
I want to know how to combine 2 column in a table into 1 column to display it in a datagrid.
For example i have firstname and lastname in the table. I need to display as Name in datagrid where it have to combine firstname and lastname. How can i do that?

And i also want to know how to change the value to display it in datagrid. In my table the value is only '1' or '0'. Where i need to display as Active when it is '1', Unactive when it is '0'. Is this do in programming way?

Thanks in advance.

View Replies !   View Related
Getting Data And Combine Two Column
I have a table Table, there are two set of Number and store in Tel1 and Tel2

I want to get all the numberof each reacod that start with '1' on each record, the number I need that may in Tel1 or Tel2, I want to get the result like 'Result' table how can I do it ?

THX

TableA
+----+-------+-------+
| ID | Tel1 |Tel2 |
+----+-------+-------+
| 1 | 12223 | 92269 |
| 2 | 12269 | 97789 |
| 3 | 96636 | 13369 |
| 4 | 12259 | 97781 |
| 5 | 92889 | 12263 |
+----+-------+-------+


Result
+----+-------+
| ID | Tel |
+----+-------+
| 1 | 12223 |
| 2 | 12269 |
| 3 | 13369 |
| 4 | 12259 |
| 5 | 12263 |
+----+-------+

View Replies !   View Related
SELECT Statement To Return Table Column Names
Is there a SELECT statement that will return a the column names of a given table?

View Replies !   View Related
Check For Column Existance Before Alter Table Statement
Hello all,

I am trying to add columns to several tables in a singular database. I want to check to see if the column exists in a given table first before executing the alter table command so that I can skip that command if the column already exists, and avoid aborting my script prematurely. I can't seem to find a way to check for the column's existance. Any suggestions?

View Replies !   View Related
Insert Statement That Pulls Column Data From Another Table?
hi.

i cant get this quite right.

i have a table and i need to insert one column with data from another table. it goes something like this (although i know this is wrong, just here for a visual explaination) :


Code:


INSERT INTO List
(list_date, email_address, list_status, list_email)
values
(
GetDate()
, 'name@rice.edu'
, 0
, SELECT emailAddress FROM Users WHERE id = '72'
)



so, list_email needs the email address from the Users
table. i tried messing around with inner joins but, well,
here i am...

thanks in advace.

View Replies !   View Related
SQL Statement Not Producing Any Error For Invalid Column In A Table
Hi,

I am using SQL Server 2005 with SP1 patch update.I have tow tables

X table fields:

ClientID,ClientName,ClientRegisteredNumber,HoldingName,HoldingRegisteredNumber,NumberOfHoldings

Ytable fields:

ClientID,ClientName,RegisteredNumber,HoldingName,HoldingRegisteredNumber,NumberOfHoldings

If i run a query for X table:

SELECTRegisteredNumberFROM X it produces the error like this

Msg 207, Level 16, State 1, Line 1

Invalid column name 'RegisteredNumber'.



But if i run the query for X,Y table:

SELECT *FROMYWHERE RegisteredNumber NOT IN

(SELECT RegisteredNumberFROM X)

It's not producing any errors.

Why this? Is this the SQL Bug or my query problem?

Can anyone explain how to solve this?

Balaji



View Replies !   View Related
Is There A Way To Combine The Results Within A Column Into One Cell?
I have a simple sql select statement that looks like this. Select   Column1+ ' ' + Column2 As SpecFROM   Table both columns are varchar's and the output i get is something like this.Spec-----------------------AaBbCc What I would like to return as my results is this: Spec--------------------AaBbCc I hope this makes sense. I can I accomplish that?Thanks!   

View Replies !   View Related
Combine Two Column In Select Statetment?
Hi

I have a staff table and it has columns like firstname, lastname etc

I did the query and it works. but firstname and lastname are too close

SELECT (FirstName + Lastname) as fullname
FROM StaffList

I need the format "firstname , lastname " so I write the second query

SELECT (FirstName + " , " + Lastname) as fullname
FROM StaffList

But it doesn't work.Please help me and let me know how to make the second query work.

Thanks a lot

Mark

View Replies !   View Related
Combine Column Fields Together Into One Field
Hello to All,

I needs help to combine these together but how would I eliminate necessarily zero in front of "PropertyHouseNumber".


Table: DirectHome

Column fields.......
PropertyHouseNumber, PropertyStreetDirection, PropertyStreetName, PropertyMODE

0000001091 , W , 000026TH , RD


Thank you


RV

View Replies !   View Related
INSERT Statement Conflicted With COLUMN FOREIGN KEY SAME TABLE Constraint
For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:


DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'


/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@@ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'


Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

View Replies !   View Related
Random Selection From Table Variable In Subquery As A Column In Select Statement
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?




SET NOCOUNT ON


Declare @RandomRecordCount as int, @Counter as int
Select@RandomRecordCount = 1000

Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)

Set @Counter = 0

while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end


Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'

Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'

Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'


select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type

from @NumericBase



SET NOCOUNT OFF

View Replies !   View Related
How To Combine Two Cloumns And Display As One Column In UI ? How To Write Query For This?
I have 2 Columns FirstName and LastName but i need to show it in UI as User Name ,that means i need to combine both First Name and Last name and display both as 1 field namely UserName ,How to query tht ? What shld i use?

View Replies !   View Related
How Can I Combine Values Of Multiple Columns Into A Single Column?


Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909T8888

And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888

where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?


View Replies !   View Related
Need To Combine String Data From Multiple Columns Into One Column
When quering a table with given criteria, For ex:

select notes, jobid, caller from contact where status in (6) and jobid = 173
I am getting this:




This job will be posted to Monster for 2 weeks. 173 906
Waiting for full budget approval 173 906
TUrns out we're uppin 173 906

What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon?

Please HELP!!!!!

View Replies !   View Related
Update One Colum With Other Column Value In Same Table Using Update Table Statement
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani

View Replies !   View Related
Combine Relationship Table
Hello everyone:

I have two database desgin questions. I hope could get some helps here to resolve my puzzle.

suppose I have a parent table which has many-to-many relationship to its three children tables. E.g:

Parent table:
webpage
-pageID
-pageTitle

child table: (which are the content could be in the webpage)

Picture
---pictureID
---pctureName

StreamVideo
--videoID
--videoName

StreamAudio
--audioID
--audioName

Normally, in a many-to-many relationship, a third table will be introduce to break down the many-to-many into one-to-many relationship. So in this case, I will have three relationship tables

1. Webpage--Picture
--pageID
--pictureID

2. Webpage--Video
--pageID
--videoID

3. Webpage--Audio
--pageID
--audioID

My question is should I combine this three relationship table into one big relationship table like:

Webpage--content
--pageID
--pictureID
--videoID
--audioID

I know the drawback of this combining will cause redundant, but this could avoid join tables when writting query (especially when there are more child table associate with one parent table). Also I was told that foreign key can be nullable, so if one webpage only have pictures, as long as I insert pictureIDs with the null value videoIDs and audioIDs into the Webpage--content table, then it seems I don't duplicate anything. So far I don't see anything wrong with this approach, I hope someone can give me some suggestions whether I should go this way.


My another question is also about the relationship table, suppose in the above exmaple, we won't combine those three relationship tables. But we need to assign a value to each pictureID, videoID, audioID. And this value will be different if a pictureID, videoID, aduioID appears in different webpage. How I should solve this issue? The best I can think so far is add a value column in the relationship table. But I have not seen people did this before. Add columns into a relationship table. Is this a good desgin?

View Replies !   View Related
Nice Problem: How To Combine Different Columns Into A Table
It's rather easy to combine resultset from the same table structure...we can either insert the entries or union the results.

But let's say you select different columns from different tables and want to combine them to form a new table, how would you do it (assuming you can't join those tables since they are not related), assuming they all return the same number of rows.

select col1 from table1
go
select col2 from table2
go

Now I want to combine them so table3 is made of col1 and col2.

View Replies !   View Related
Combine 2 Rows From Derived Table Into 1 Row W/o Repeating Query?
I'm trying not to use a temp table, but i may have to do so..
I'm using sql2005 for this case.

i have a derived table that makes the following results:



ID Status Name

2 1 "A"

2 2 "B"



I want to get the following:

ID Name1 Name2

2 "A" "B"



but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.

View Replies !   View Related
Combine 2 Rows From Derived Table Into 1 Row W/o Repeating Query?


I'm trying not to use a temp table, but i may have to do so..

i have a derived table that makes the following results:

ID Status Name
2 1 "A"
2 2 "B"

I want to get the following:
ID Name1 Name2
2 "A" "B"

but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.

View Replies !   View Related
Using Column Number Inplace Of Column Name In SQL Select Statement
Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep

View Replies !   View Related
How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View Replies !   View Related
Problem With Comparing A Table View Column With The Table Column?
I have a CVdetails table which all the personal and technical details along with the path called CV_Path which stores the path of CV word doc. Using Index Server Query i got the paths in my SQL server using Linked Server. I used this Query
 
SELECT Path FROM OPENQUERY (FileSystem,'SELECT Directory, FileName, DocAuthor, Size, Create, Write, Path FROM SCOPE ('' "c:myCatalogFolder" '') WHERE FREETEXT (‘’reference’’)’)
Which gave a result as
 
Path





c:mycatalogfolderajaxguide.doc


c:mycatalogfolderquickguide.txt 
 
 Now, I want to compare these paths with the ones in the CVdetails table which are same and drag out the relevant CVdetails of that particular path. My code is  shown below but its not working out, its not giving any results though the paths are same. I hope I made some sense till now
Thanks in Advance
 
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
GO
CREATE VIEW FileSearchResults
 AS
SELECT *
 FROM OPENQUERY (FileSystem,'SELECT Directory, FileName, DocAuthor, Size, Create, Write, Path FROM SCOPE ('' "c:myCatalogFolder" '') WHERE FREETEXT (''reference'')')
Go
SELECT * FROM FileSearchResults F, CVdetails C WHERE C.CV_Path = F.PATH
Go
 

View Replies !   View Related
How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?
Hi, all experts here,



Thanks for your kind attention.



I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,





View Replies !   View Related
Questions On Key Column In Case Table And Key Time Column In Nested Table Using Time Series Algorithm
Hi, all experts here,



Thank you very much for your kind attention.



I am confused on key column of case table and key time column of nested table by using Time Series algorithm.

In my case, the case table structure is as below:

Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),

While the nested table structure is as below:

Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)

So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?

Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?



Hope it is clear for your advices and help.

And I am looking forward to hearing from you shortly.



With best regards,



Yours sincerely,







View Replies !   View Related
TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying tosolve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;









View Replies !   View Related
Combine Data In Single Row From Single Table
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date


Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000


Should Appear


Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View Replies !   View Related
A Table/column To Table/column Data Check (was &"Help Please, SQL Something Simple&")
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.

View Replies !   View Related
ALTER TABLE DROP COLUMN LastUpdated Failed Because One Or More Objects Access This Column.
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
 

View Replies !   View Related
Cannot Insert The Value NULL Into Column SnapshotDataID, Table ReportServerTempDB.dbo.SessionData; Column Does Not Allow Nul
I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restorefrom SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks

View Replies !   View Related
Nested Table Key Column Is Not Bound To An Input Rowset Column
Hi!
I have a "little" problem with nested case model:



-- "normal" database:

DROP TABLE [unitInfo] ;

GO

CREATE TABLE unitInfo (

unitID INT PRIMARY KEY

, beginDate SMALLDATETIME

, area VARCHAR(10)

, partSize INT

, y2predict MONEY

) ;

go

INSERT INTO unitInfo

VALUES (1, '2007-02-01', 'home', 42, 10.0) ;

INSERT INTO unitInfo

VALUES (2, '2007-03-05', 'home', 43, 11.0) ;

INSERT INTO unitInfo

VALUES (3, '2007-02-02', 'office', 11, 11.4) ;

INSERT INTO unitInfo

VALUES (4, '2007-02-01', 'office', 10, 33.6) ;

INSERT INTO unitInfo

VALUES (5, '2007-02-01', 'office', 42, 44.1) ;



CREATE TABLE unitLog (

id INT IDENTITY(1, 1)

PRIMARY KEY

, logtime SMALLDATETIME

, -- combination of logtime/unitID is unique

unitID INT

, -- "FK" on unitInfo

m1 FLOAT

, m2 FLOAT

)





INSERT INTO [unitLog]

VALUES ('2007-01-01', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-01', 2, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-01', 3, 63.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-02', 4, 432.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-02', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-03', 1, 423.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-04', 1, 432.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-05', 2, 43.0, 441.0)

INSERT INTO [unitLog]

VALUES ('2007-01-06', 2, 43.0, 4.0)

INSERT INTO [unitLog]

VALUES ('2007-01-06', 3, 43.0, 4.0)

INSERT INTO [unitLog]

VALUES ('2007-01-07', 1, 4.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 3.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-08', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-09', 2, 143.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-10', 3, 143.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-11', 4, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-11', 5, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-12', 2, 43.0, 144.0)

INSERT INTO [unitLog]

VALUES ('2007-01-13', 4, 413.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-14', 4, 43.0, 414.0)

INSERT INTO [unitLog]

VALUES ('2007-01-14', 1, 43.0, 44.0)

INSERT INTO [unitLog]

VALUES ('2007-01-20', 1, 43.0, 414.0)

INSERT INTO [unitLog]

VALUES ('2007-01-22', 1, 43.0, 414.0)



-- SSAS:

CREATE MINING STRUCTURE NestedStructure

( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE

, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS

, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS

, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS

)

)

ALTER MINING STRUCTURE NestedStructure

ADD MINING MODEL nestedModel ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR

,y2predict REGRESSOR PREDICT_ONLY

, logdata ([id] , unitID

, m1, m2

)

) USING Microsoft_Decision_Trees

/* version 1*/

insert into NestedStructure ( unitID, beginDate, area, partSize, y2predict

, logdata(skip,unitID, m1, m2))

openrowset('sqloledb', Server=myserver;Trusted_Connection=yes;,

'Shape {select * FROM mydb.dbo.unitInfo }

Append ( { select id, unitID, m1, m2 from mydb.dbo.unitLog }

Relate unitID to unitID ) as logdata ')

Parsing the query ...

OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.

Parsing complete

Where is the error?



/*version 2*/

CREATE MINING STRUCTURE NestedStructure1

( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE

, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS

, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS

, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS

)

)

ALTER MINING STRUCTURE NestedStructure1

ADD MINING MODEL nestedModel1 ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR

,y2predict REGRESSOR PREDICT_ONLY

, logdata ([id] , unitID

, m1, m2

)

) USING Microsoft_Decision_Trees



insert into mining structure NestedStructure1 ( unitID, beginDate, area, partSize, y2predict

, logdata(skip,unitID, m1, m2))

Shape {openquery(dsnDB,'select * FROM mydb.dbo.unitInfo') }

Append ( { openquery(dsnDB,'select id, unitID, m1, m2 from mydb.dbo.unitLog') }

Relate unitID to unitID ) as logdata



Parsing the query ...

Error (Data mining):

INSERT INTO error: The '[logdata].[id]' nested table key column is not bound to an input rowset column.

Parsing complete








Remark that combination logtime/unitID is the natural key in unitLog.
"ID" is the surrugate key.

What is wrong here...?



View Replies !   View Related
Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

Table structure as follows



Employee

Empno empname salary
commission



I want to have an other employee table named employee_modified

Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)

Is that possible to do it.. or am I doing something wrong.



something like



Select empno , empname , salary , commission,

(salary + commission) as derived_colum1 ,

(derived_colum1 + xxxxx) as derived_colum2 ,

(derived_colum2 + xxxxx) as derived_colum3

into employee_modified from employee

View Replies !   View Related
Table Column Names = Dataset Column Values?!


I need to create the following table in reporting services



PRODUCT April March Feb

2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6





My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???


Thanks in advance!!!


View Replies !   View Related
Column With Select Statement
Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count? Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this. 

View Replies !   View Related
Alter Table Add Column - How Do You Add A Column After Say The Second Column


When you use "Alter Table add Column", it adds the column to the end of the list of fields.



How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?



Create table T1 ( a varchar(20), b varchar(20), c varchar(20))



Alter table add columnxvarchar(20)

so that the resulting table is

T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)



Can this be done programmatically?















View Replies !   View Related
If Statement In Computed Column Specification
I want to use an if statement to compute the value of a column in SQL Database using other columns. I am supposed to check if a column is null or not and do the computations accordingly. Can anyone help?
 

View Replies !   View Related
Pass Column Name Using Parameter In SQL Statement...
Hi,I am trying to Pass Column Name(FieldName) using Parameter in SQLStatement... But i am getting error...how can i pass Column name using parameter???Example:in table i have fieldname ECountry...Select @FName='ECountry'Select @FName from Table...How it works???Thanx in Advance,Regards,Raghu...

View Replies !   View Related
Possible To Parse A Column In A Select Statement?
I have a column called SEGMENTED_BLOCK sample data:X,X,XXX,XX,XX,TYZC123456,X,X,TOYZ654321,1234,777777I need to do something that has the effect ofSELECT(stuff before first comma) as FIRST_ITEM,(stuff after first comma, but before second) as NEXT_ITEM,(stuff after second comma but before third(if any)) as THIRD_ITEMFROM SEGMENT_XREFWHERE LOOKUP_ITEM = 12345ORDER BY FIRST_ITEMFIRST_ITEM is pretty easy, but it gets uglier fast.My attempts are horrendously ugly nested checkindex and substring statements.Is there an easier way?

View Replies !   View Related
Return SP In A SELECT Statement Column
I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement.


I want the following output:


CatID | CatTitle | CatTree
001 | News | exec sp_DisplayTree(@CatID)


My code I tried doesn't work:


SELECT
C.CatID As CatID,
C.CatTitle As CatTitle,
CatTree = (exec sp_DisplayTree C.CatID)
FROM
Cats As C WITH (nolock)



I cannot find a solution to my solution, please help...

View Replies !   View Related
Select Statement Using Column Numbers
I have a number of lookup tables in my db.
Each table typically has an ID column and a 'lookup values' column.
I want to create a scalar function wherein, by supplying the table name and the ID, I want to retrieve the lookup value and vice-versa. I donot want to create seperate function for each table, which is easy. One function has to return values given the table name and the value of the ID or lookup value.
The problem being faced is how to construct the select statement.
An Execute statement created out of column names, retrieved with Col_Name(), is not useful as Execute statement is not permitted inside a a function.

View Replies !   View Related
Can You Use Column Order Value In Select Statement
Is there a shortcut to spelling out column names when you are doing a select statement?

For instance could you write Select 1, 5, 6 from table where whatever...

I tried this but didn't get any results so if you can I must be using wrong syntax.

Thanks
!

View Replies !   View Related
Computed Column Spec :: IF Statement
so i want to create a cmoputed column specification based on an 'if' condition. this is what I want to happen:

if [WID] < 10, I want the value in the computed column to be "W0" + [WID]
if [WID] >= 10, I want the value in the computed column to be "W" + [WID]

[WID] is of type tinyint.

What is my computed column specification formula? Thanks.

View Replies !   View Related
How Do I Use A Variable To Specify The Column Name In A Select Statement?
How do Iuse a variable to specify the column name in a select statement?

declare @columnName <type>

set @columnName='ID'

select @columnName from Table1

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved