|
You could do this using PIVOT but you would need to supply fixed/known columns (Sampling Sites). Dynamic cross-tabulation of data in the way that you require is handled by Analysis Services or by importing the data into Excel and using the pivot functions there.
Ian
|
|
|
|
|
i.j.russell wrote: Dynamic cross-tabulation of data in the way that you require is handled by Analysis Services or by importing the data into Excel and using the pivot functions there.
Thanks for the reply. After researching the matter some more, I came to the same conclusion.
Funny thing is though, I'm constructing the data so that I can export the result to Excel.
Marc
|
|
|
|
|
I've got this query:
select
Species.Name as Species_Name,
Species.CommonName as CommonName,
Family.Name as Family_Name,
NativeView.NativeCode
from Species
left join NativeView on SpeciesID=Species.ID
inner join Family as Family on Species.FamilyID=Family.ID
That uses this view:
create view NativeView as
select
Species.ID as SpeciesID,
SpeciesNativeTypeOptionList.Code as NativeCode
from Species
inner join SpeciesTypeInfo as SpeciesNative on Species.ID = SpeciesNative.SpeciesID
inner join SpeciesTypeOptionList as SpeciesNativeTypeOptionList on SpeciesNative.SpeciesTypeOptionListID=SpeciesNativeTypeOptionList.ID
inner join SpeciesTypeList as SpeciesNativeTypeList on SpeciesNativeTypeOptionList.SpeciesTypeListID=SpeciesNativeTypeList.ID and SpeciesNativeTypeList.Name='Native'
But what I'd like to do is formulate the query without using a view at all.
The problem comes in on the "left join" above, because I want "NativeCode" to return null when the species ID does not have a matching row in the query that makes up the NativeView.
Is it possible to achieve that (get rid of the view)?
Thanks!
Marc
|
|
|
|
|
Here:
select
Species.Name as Species_Name,
Species.CommonName as CommonName,
Family.Name as Family_Name,
v.NativeCode
from Species
left join
(select
Species.ID as SpeciesID,
SpeciesNativeTypeOptionList.Code as NativeCode
from Species
inner join SpeciesTypeInfo as SpeciesNative on Species.ID = SpeciesNative.SpeciesID
inner join SpeciesTypeOptionList as SpeciesNativeTypeOptionList on SpeciesNative.SpeciesTypeOptionListID=SpeciesNativeTypeOptionList.ID
inner join SpeciesTypeList as SpeciesNativeTypeList on SpeciesNativeTypeOptionList.SpeciesTypeListID=SpeciesNativeTypeList.ID and
SpeciesNativeTypeList.Name='Native'
) v
on v.SpeciesID=Species.ID
inner join Family as Family on Species.FamilyID=Family.ID
The thing I was missing was the alias for the inner select. I've been learning that when SQL says there's an error with ')', it means I'm missing an alias name.
Marc
|
|
|
|
|
in sql server have the 2 tables first table have the 5 rows and second table have the 10 rows
when we apply the left outer join the tables . whats the no of rows selected in best case and worst case can u explain this?
raja
|
|
|
|
|
Post your query and we will see what's wrong.
I Love T-SQL
|
|
|
|
|
|
sqlserver have the 2 tables 1 st table have the same strcture with the second table ,when we insert the first table it will also update the second table and when we update the first table it will also update the second table
raja
|
|
|
|
|
Values which you insert on first table insert and in second table too, same way use for update.
I Love T-SQL
|
|
|
|
|
Use Triggers for this
Happy programming
Dont Get Paid for the Hours you worked, Get Paid for the Work You Have Done in an Hour.
|
|
|
|
|
Hi all,
I made a very simple program to manipulate with DB as below
1. I have a SQL Server DB. The database contains just only 2 tables
1.1 TEST1(KEYCODE, SEIHIND, TEXT1, TEXT2, TEXT3), KEYCODE is primary key, auto increase
1.2 TEST2(TEXT1, TEXT2, TEXT3) no key
2. I make a new MDB by MS Access 2007 and created link table to these above SQL DB via ODBC(SQL Server driver)
3. My program uses DAO 3.6 to insert data to SQL via MS Access
3.1 The bussiness is to select data from TEST2 and insert to TEST1
3.2 SQL is like
INSERT INTO TEST1(SEIHIND, TEXT1,TEXT2,TEXT3) SELECT '1', TEXT1, TEXT2, TEXT3 FROM TEST2
4. To obtain above, i made a simple program like below:
Private Sub Command1_Click()
Dim defWsk As DAO.Workspace
Dim dbs As DAO.Database
Dim sqlStr As String
Set defWsk = DBEngine.Workspaces(0)
Set dbs = defWsk.OpenDatabase("F:\Tin\PQMC.MDB")
defWsk.BeginTrans
sqlStr = "INSERT INTO TEST1(SEIHIND, TEXT1,TEXT2,TEXT3) " & _
"SELECT '1', TEXT1, TEXT2, TEXT3 " & _
"FROM TEST2 "
Debug.Print sqlStr
dbs.Execute (sqlStr)
defWsk.CommitTrans
End Sub
5. Issue: The problem when execting above code is "data is not inserted to TEST1, TEST1 is blocked(I could not execute SELECT * FROM TEST1 in SQL Analyser)" When i close program, table is not locked(I could execute SELECT * FROM TEST1 in SQL Analyser)
Notes: I take above SQL and execute in SQL Analyser. it's OK
Anyone know how to solve this problem, please help me. Thank you very much for your support.
Tin Le,
|
|
|
|
|
Hi all,
I am Savtri here.I am having 3 field in table,Slnumber,Name, and PhoneNumber.Slnumber is of type Autonumber, and Name and phonenumber are text.when I add New element then it is incrementing.but when i am deleting it is not decrementing it means if i have 5 values like 1,2,3,4,5.if delete 3rd value then my table cantains 4 values.so please tell me How Shall i do it.PLEASE TELL ME.HELP ME.
Thanks in Advance,
savitri
|
|
|
|
|
this is what I suggest you:
Don't set column SInumber as AutoNumber, set value by code, when you insert row get max of SInumber and increase by 1.
When you delete one row reset values of column SInumber by this method to get regular numbers e.g:1,2,3,4, and so on:
declare @i as int<br />
set @i = 0<br />
declare @rowCount as int<br />
set @rowCount =(select count(SInumber) from myTable) <br />
<br />
while @i <= @rowCount <br />
begin<br />
set @i = @i +1<br />
<br />
if(select count(SInumber) from myTable where SInumber =@i)=0 <br />
begin<br />
Update myTable set SInumber = @i where SInumber =@i+1 <br />
end <br />
end
I Love T-SQL
|
|
|
|
|
That is the way autonumbers work - if you think about it has to. What if you had used the Slnumber as a key in a related table? Changing the numbers in the first tabe would orphan records in the second table.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How can i write a program to execute a project by scheduling?
For eg.
I need to write a program which will run in a defined time. also it should stop the execution in a given time.
Thankyou,
Yesuprakash
|
|
|
|
|
Good luck with finding any SQL statement that will do that for you.
[Hint]You're in the wrong forum[/Hint]
|
|
|
|
|
Maybe collaborate with this poster clickety
He had the same question as you.
Guy 
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
hi all,
im realy new in db programing.
i have a database file there is "mean table" and there is two fields short field and fullform field.
----------------------------------------------------
ID|SHORT| FULLFORM
----------------------------------------------------
1 | CPU | central proccesing unit
2 | RAM | random access memory
3 | HDD | Hard disk drive
----------------------------------------------------
my problem is this
i have a window with textbox1 and textbox2 and search button
when user type in textbox1(CPU) and press search button
in textbox2 need to display "central proccesing unit"
how to do please help
|
|
|
|
|
select * from myTable where short='CPU' and FullForm='central proccesing unit'
instad CPU value and central proccesing unit value set your textbox values
I Love T-SQL
|
|
|
|
|
hi
What do you are development tools?
C#?
|
|
|
|
|
hi zhq,
yes im using visual c# 2005.
actualy im creating computer dictionary.
actulay i have windows form with comboBox(simple style) and other one is textBox(multiline style).
I have dicdat.mdb database there are two fields(word and meaning field).
initialy when application run all word field will be display and when user type some word at combiBox and hit enter it sould be display meaning at textbox.
please if you have some source or clue please help me out.
............
other ine problem is some time its display at textbox but there is < div > code is appear just like : < div > random access memory.< div >
what is ther problem.
sincerly
bir
|
|
|
|
|
Hello,
I'm all new to this programming but i'm gently getting the grasp of things, but sometimes feel i'm stepping ahead of myself. Just a quick question, i'm trying to create a search string based on certain choices that the user makes. The user is able to select all the choices or nothing at all or parts of the choices. My problem is I think I'm goin about it the wrong way below is the string that i have created that creates an sql search string, based upon the users choices.
Dim search_str As String = "SELECT * FROM Properties WHERE"
If ddl_type.SelectedValue <> "0" Then
search_str += "Property_Type =" + ddl_type.SelectedItem.Text
End If
If ddl_location.SelectedValue <> "0" Then
search_str += "AND Property_Location =" + ddl_location.SelectedItem.Text
End If
If ddl_bedrooms.SelectedValue <> "0" Then
search_str += "AND Property_Bedrooms =" + ddl_bedrooms.SelectedItem.Text
End If
If ddl_max_price.SelectedValue <> "0" Then
search_str += "AND Property_Price <=" + ddl_max_price.SelectedItem.Text
End If
If ddl_min_price.SelectedValue <> "0" Then
search_str += "AND Property_Price >=" + ddl_min_price.SelectedItem.Text
End If
For i = 0 To cbl_features.Items.Count - 1
If cbl_features.SelectedItem.Selected = True Then
search_str += "AND Property_Features Like" + cbl_features.SelectedItem.Text
End If
Next
could someone have a look at this and check to see if this is the best method for creating this search, or possibly suggest an alternative.
Regards
Paul Mc Gann
|
|
|
|
|
Your approach is valid for the situation, but you are leaving yourself wide open to be hacked. Try this instead:
<br />
Dim search_str As String = "SELECT * FROM Properties WHERE"<br />
Dim parameters As ArrayList = new ArrayList()<br />
<br />
If ddl_type.SelectedValue <> "0" Then<br />
SqlParameter p = new SqlParameter("@Property_Type", SqlDbType.whatever)<br />
p.Value = Server.HtmlEncode(ddl_type.SelectedItem.Text)<br />
search_str += "Property_Type = @Property_Type"<br />
parameters.Add(p)<br />
End If<br />
<br />
If ddl_location.SelectedValue <> "0" Then<br />
SqlParameter p = new SqlParameter("@Property_Location", SqlDbType.whatever)<br />
p.Value = Server.HtmlEncode(ddl_location.SelectedItem.Text)<br />
search_str += "AND Property_Location = @Property_Location"<br />
End If<br />
<br />
' and so on and so forth....<br />
<br />
SqlCommand cmd = new SqlCommand(search_str)<br />
For i = 0 To parameters.Count - 1<br />
cmd.Parameters.Add(CType(parameters(i), SqlParameter))<br />
Next<br />
<br />
' execute your query and return the result ....<br />
If you want more information on why do a search for "Sql Injection" and "Cross site scripting" or "XSS". There are also performance reasons for using parameters. Here are some codeproject articles on these topics:
Sql Injection[^]
Cross site scripting (aka XSS)[^]
Ad hoc query performance[^]
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
thanks mark much appreciated, the site is only to run on my localhost but it's worth having a look at the articles for future projects.
|
|
|
|
|
If this is the route you have chosen to go...
Mc--Gann wrote: If ddl_type.SelectedValue <> "0" Then
search_str += "Property_Type =" + ddl_type.SelectedItem.Text
End If
There's the possibility that ddl_type.SelectedValue will be 0, in which case, your sql statement will have a syntax error.
For example:
Mc--Gann wrote: Dim search_str As String = "SELECT * FROM Properties WHERE"
If ddl_type.SelectedValue <> "0" Then
search_str += "Property_Type =" + ddl_type.SelectedItem.Text
End If
If ddl_location.SelectedValue <> "0" Then
search_str += "AND Property_Location =" + ddl_location.SelectedItem.Text
End If
might result in
search_str = "SELECT * FROM Properties WHERE AND Property_Location =" + ddl_location.SelectedItem.Text
Also, check that you have a space prefixed in each of the concatenations...
If ddl_location.SelectedValue <> "0" Then<br />
search_str += " AND Property_Location =" + ddl_location.SelectedItem.Text<br />
End If
HTH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
