|
Thanks yaar. It works..
But can we get a output like this :
1945 1950 1960
t1 t4 t6
t2 t5 t7
t3
i hv also added group by. But it doesn't work..
|
|
|
|
|
What you are trying to achieve (I think) is known as pivoting. This sample should point you in the right direction.
create table UserArea(Country varchar(20))
insert into UserArea
select 'India'
union all
select 'USA'
union all
select 'India'
union all
select 'UK'
DECLARE @SQL nvarchar(4000)
SET @SQL=''
SELECT @SQL= @SQL +
'SUM(CASE WHEN Country=''' + a.Country + ''' THEN 1 ELSE 0 END) AS [' + a.Country + '],'
FROM (select distinct Country from UserArea) as a
select @SQL = left(@SQL,len(@SQL)-1)
SET @SQL='SELECT ' + @SQL + ' FROM UserArea'
EXEC(@SQL)
drop table UserArea
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi Niraj Here ,
I hope this will work for you....
try this..
Select fieldnamewhich containst1t2ec as [1945]
From Tablename
Where year = 1945
Have a noce Day..
Stay Tune .....
Take Care....
|
|
|
|
|
Any ideas? I'm coming across this, and it makes no sense to me.
Marc
|
|
|
|
|
i wasnt aware that you could create a PK that wasnt clustered, i thought they were all clustered indexes.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
In SQL Server they are clustered as default. But you can make then non-clustered and use the clustered index for yourself.
modified on Friday, June 20, 2008 12:20 AM
|
|
|
|
|
makes sense...thanks, but i'm going to assume they arent doing that in the table Marc's looking at.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
As I'm sure you know - it depends on your database design and how you access the tables.
I think they are made this way by default to ensure at least some "workable" indexes for default databases from people who haven't looked too much into indexation.
Because if you mainly access a table via - for example - foreign keys and extract 2 or 3 specific rows of data, it makes sense - to me at least - to make that your clustered index.
Also if your PK is primarily an identity or similar number which mattes little then I would also say it makes sense to keep that index non-clustered, because it usually means you'll access data not in the index anyway and the benefits of the clustered index diminishes.
However if your primary key consists of "relevant data" and you need to access this data only - and often - then it is beneficial to have it as clustered.
In my opinion - it is as all things with databases, a case-by-case evaluation, but as mention first - my main guess is that it is made to simply have some "default" clustered indexes for the default databases, and those who know what they are doing will evaluate whether it is a beneficial index and change it if not anyway.
|
|
|
|
|
One instance I have seen is when using a GUID as a PK field.
|
|
|
|
|
select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Calls]
from status join empmaster on empmaster.empid = status.empcode
where empmaster.Permission <> '1' and logintime between '06/02/2006' and '06/03/2006'
group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by empcode asc
select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Contacts]
from status join empmaster on empmaster.empid = status.employeecode
where empmaster.Permission <> '1' and logintime between '06/02/2008' and '06/03/2008' and
statuscode not in (11)group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by emp asc
i have these two queries.the problem is how to join these queries?
can any one help me.if i write two queries as one,data of two columns ( [no of calls] and [no of contacts]) resulting same count.
|
|
|
|
|
use UNION
select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Calls]<br />
from status join empmaster on empmaster.empid = status.empcode<br />
where empmaster.Permission <> '1' and logintime between '06/02/2006' and '06/03/2006' <br />
group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by empcode asc<br />
<br />
UNOIN<br />
<br />
select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Contacts]<br />
from status join empmaster on empmaster.empid = status.employeecode<br />
where empmaster.Permission <> '1' and logintime between '06/02/2008' and '06/03/2008' and<br />
statuscode not in (11)group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by emp asc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Use "Union All" instead of "Union" if your 2 queries doesn't return duplicates.
|
|
|
|
|
Yes you are right, I forgot to write UNION ALL in both two places on my first response.Thanks for correcting me dude.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
No problem. You beat me to the solution anyhow 
|
|
|
|
|
if u wants to run both qries one after another
then the Solution is....
SqlQry 1
Go
Sqlqry 2
Go
.
.
.
.
Try Go Statement in SQL it will help you...
Enjoy your coding.. 
|
|
|
|
|
Hi guys,
I have the following query, example
Select a.id, b.description
from table1 a
left join (select top 1 c.id, c.description from tableb c where c.id = a.id) as b on b.id = a.id
The reason I do this is because tableb could have multiple duplicates of c.id and I want it to only do the select on one specific id from the table1.
But I get the error because a.id is not within the select statement of tableb.
Is there any way that I can do this, if I remove the where clause in the tableb select statement it returns duplicate values on the description.
Thanks in advance
No matter how long he who laughs last laughs, he who laughs first has a head start!
|
|
|
|
|
Try this
Select a.id<br />
(select top 1 c.id from tableb c where c.id = a.id) ,<br />
(select top 1 c.description from tableb c where c.id = a.id) <br />
from table1 a
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Works, Thanks
No matter how long he who laughs last laughs, he who laughs first has a head start!
|
|
|
|
|
No problem,I am glad that worked for you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I have to be able to select het highest number smaller than a certain number
for example I have in DB
1
2
5
6
7
and I want the highest number smaller than 5 (in this case 2) but can't seem to get this working (at least not in 1 sql statement)
I can get the numbers 1 and 2 (is easy) but want to get only the number 2
any help would be appriciated
|
|
|
|
|
TDDragon wrote: I have to be able to select het highest number smaller than a certain number
for example I have in DB
1
2
5
6
7
and I want the highest number smaller than 5 (in this case 2) but can't seem to get this working (at least not in 1 sql statement)
I can get the numbers 1 and 2 (is easy) but want to get only the number 2
any help would be appriciated
Select Max(ID) where ID<5
Does that work for you?
|
|
|
|
|
I tried this yesterday but it returned an empty row
tried it again today and now I do get an value (think my mind is playing tricks on me  )
or the fact that I had a second column in the select statement was cousing trouble I don't know anyway it works now
Thank you for you time
|
|
|
|
|
ChandraRam is correct, but you can use this query too:
select top 1 id from tablename where id < 5 order by id desc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Wonder why I didn't think of that...  
|
|
|
|
|
Hi everyone,
I am developing a asp.net c# application. I am a beginner to MSMQ. I need to save the data(username, password) into msmq. In the particular period of time, I need to transfer that data(username, password) to MySQL database due to making the fast data access. How is it possible. Please help me regarding this. The best elaborated answer with some examples would be appreciated.
Thanks in Advance,
J. Mohan.
I see the God Face in Helping people
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
