|
Try something like this
create table #b1(usr sysname, flag char(1), val float)
insert into #b1(usr,flag,val) select 'a','n',22
insert into #b1(usr,flag,val) select 'b','Y',22
insert into #b1(usr,flag,val) select 'a','n',11
insert into #b1(usr,flag,val) select 'b','n',2
insert into #b1(usr,flag,val) select 'a','Y',100
select usr,sum(case when flag = 'y' then val else val * -1 end) from #b1 group by usr
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi friends
I have two table , name of the table is Table1, table2
table1 values
**************
rollno name tot
1 a 100
2 b 75
3 c 76
table2 values
*************
rollno reference_name
1 SSSS
1 RRRR
2 MMMM
i written the query like this
select table1.rallno,table1.mark,table2.refrence_name from table1 left join table2 on table1.rallno=table2.rallno
I want all the vale from Table1 and Table2 Roll No 1 has more reference name but I want only 1 Reference name
How can i write the query plz tell
|
|
|
|
|
If I understood you correctly, you're having a problem with the query because, based on your sample material, it returns 4 rows instead of 3.
If this is correct, you should use for example GROUP BY clause, but then you cannot have individual values from table2, only aggregates. So you should choose what do you want from table2. For example:
select table1.rallno,
table1.mark,
max(table2.refrence_name)
from table1 left join table2 on table1.rallno=table2.rallno
group by table1.rallno,
table1.mark
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi, a newbie to asp.
I have the following code which when run complains with error 'expected end of statement.'
What it is suppose to do is read in the ip address of a remote host and then do a comparison to an ip field within the database. It should also time stamp this entry if logic permits.
]]>
Would appreciate some help from anyone of you gurus of asp,sql out there.
Regards Phil.
|
|
|
|
|
please provide me with a link for studying all concept and features of a datatable
|
|
|
|
|
|
Very comprehensive link!
Should cover all the areas specified. 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all ;
I have two tables Table1 and Table 2 (I work with sqlserver 2005)
Table 1:
champ1---- champ2 ---- champ3
10 ---------- 14---- aaa
10 ---------- bbb----- 15
10 ----------- 13---- ccc
Table2
champ1 ------- champ4 -------- champ5
10 -------------wpm -------------- 1000
10 -------------- xcv -------------- 1200
I try to have the following results:
field1 ------ yyyy------ bbb -----cccc ------ wpm------ xcv
10----------- 14------------ 15 ------ 13-------- 1000 ----- 1200
I made half with pivot table:
field1 yyyy ------ ------ ----- bbb cccc
10----------- --- 14--------- 15 ------ 13 --
but I do not know how to add two columns wpm and xcv
help me thank you.
|
|
|
|
|
Without seeing the query you have already built, answering the question is quite difficult, so what's your query like?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
i have to use MySQL Database as beckend for asp.net 2.0 web application can any body guide me basics of MYSQL dataabse Desgin . any article or help is highly apperciated .
Best reagrds
Rameez
|
|
|
|
|
Database design is a huge subject, regardless of the server used. Try Google, there are many, many articles and books that will help you.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
mysql provides u wid very friendly and easy to use interface.If u have ever logged in u might feel it right. Try creating a new database in it and then u can create tables and other stuff u want inside ur database. Using mysql is more like using IE.
well but the connection string is a bit lenghty.U use a odbc data provider for mysql.
connection string is:
"Driver =MYSQL ODBC <version> Driver ; Server= <servername> ; port=<port>;
option= 16387; Database=<database>; uid=<userid>;
Pwd=<password>;"
|
|
|
|
|
The relation databases should change the existing structure to tree like structures so that items of a sub item should be clearly represented
|
|
|
|
|
Ummm, well and what is the question?
Regards
Sebastian
|
|
|
|
|
If I understood correctly, you want to create a recursion on a single table. For example like in Person table it could have a reference to boss. Something like:
CREATE TABLE Person (
PersonID int,
Name varchar(50)
BossID int REFERENCES Person (PersonID)
)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I've got to give you 5 for interpretation alone, I wonder of that is actually whet the Op was after, I have no idea what was being asked.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks, it was a guess based on few keywords like tree and sub item, but let's see. The answer may be off-topic.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I guess it's the elephant, right?
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
SQL Server 2008 supports the HierarchyId. Check it out;
http://msdn.microsoft.com/en-us/library/bb677290.aspx
|
|
|
|
|
hello all
i dont know much about DB Tables but a few days ago i had problem with some methods that retrive very complex results from several tables to generate one simple table
so i was thinking and told my co-worker why dont we create a table with dynamic number of columns and rows insted of rows only, all of them said it is bad idea and no one explaines why to me. and i have been surfing the internet for few days trying to figure out whats wrong with it. so if there is any trouble, anything at all? ca you please tell me
thank you
|
|
|
|
|
Hi,
generating tables with a dynamic number of columns is not really easy to achieve. One idea behind relational databases is, that you have fixed columns but a dynamic number of rows. So to implement sth. that will create and handle dynamic columns is (in my opinion) hard.
But let's have a look at the problem itself. What do you mean by "...from several tables to generate one simple table"? Is it "join" or something like this?
Regards
Sebastian
|
|
|
|
|
SeMartens wrote: But let's have a look at the problem itself. What do you mean by "...from several tables to generate one simple table"? Is it "join" or something like this?
i am not generating a DB Table, i am trying to generate a HTML and it will be sperated into several areas for both columns and rows
SeMartens wrote: that will create and handle dynamic columns is (in my opinion) hard.
this is what i ment, and actually it is not that hard to implement.
thank you anyway, but can you give me a spesific answer or more detailed ?
|
|
|
|
|
alright, then i misunderstood you.
You don't want dynamic columns in a database table, but in a html table? This could be done using asp.net. There you could create an asp:table and add columns to it (as much as you like).
Is it this what you meant?
|
|
|
|
|
Hakmeh Mohannad wrote: i am not generating a DB Table,
Discussing a table in a database forum would naturally lead to this assumption. In which case your collegues are correct.
Generating dynamic columns for output is fairly straight forward, look into pivot/unpivot in BOL. You could then bind the result to a datagrid. Caveat is if there are lots of columns your grid is too wide for a page.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Experts
Can You Tell Me How To Make Certificate in SQL Server 2005.
And Use It In Client End
Thank u
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
