|
There is no aggregate function to do what you want, it is a concatenation of the varchar fields, and there is no function available to do this. It would be nice if there was one as we see this requirements about once a a week here!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can anyone teach me or show me some tutorials how to connect.. I need it badly..
|
|
|
|
|
Try this site
clickety[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
For example, given a table, I want to group it every 10/20/30 records per group.
One medthod is that create one view for each cases, in the example, I need create 3 views.
Because the views for the same exception the count of records per group, how to unify them
(take the count per group as a parameter)?
Thanks.
system
|
|
|
|
|
You cannot use a parameter with a view, it needs to be a stored proc.
Having said that you can do something silly like store the value in a settings table and then create a view that uses that value in the where clause thereby allowing you to change the grouping from elsewhere. Seems like another weird requirement to me, but you often see them here.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi!
I am facing problem in INSERT statement of SQL 2005 express. Actually I want to save data from one table to another two tables using SQL statement. I want to do something like that, when I will press Create button from DataGride Column. The data of that row of that Registration table will insert in some other table like Account and Directory. I have tried but I cannot do it. Please help me if anybody can.
|
|
|
|
|
Structure of two tables must be same for code down below,if structure is not same then in select command you have to specify columns which corresponding with destionationtable.
Insert into DestionationTable select * from SourceTable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
there ae three tables, i want to save data from one table to other two tables using SQL query. Please help me
|
|
|
|
|
He did, gave you the exact syntax for copying data from 1 table to another, surely you can work out how to copy from 1 table into 2 destination tables (hint call the script twice)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have give clue how to solve problem but sometimes some people are lazy and want ready solutions from others.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
do what blue_boy said.
it´s simply and fast but you can do also as following, may need in future
declare @id_t1 int
set @id_t1 = (select id from my_table1 where id = @whatever)
declare @f_name_t1 varchar(50)
set @_name_t1 = (select f_name from my_table1 where id = @whatever)
declare @l_name_t1 varchar(50)
set @l_name_t1 = (select l_name from my_table1 where id = @whatever)
insert into my_table2 (id,f_name,l_name) values (@id_t1,@f_name_t1,@l_name_t1)
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Thanks!
I think it will help me
|
|
|
|
|
When I run the following query
SELECT COUNT(*) AS Expr1
FROM Current
GROUP BY Amount
HAVING (Amount > .09)
I get two answers. I need to either add them together somehow in the SQL statement or get SQL to only return the total rather than placing them on separate rows.
|
|
|
|
|
SELECT .... FROM ... GROUP BY Amount will return the count(s) for each group. i mean
amount1 count1
amount2 count2
amount3 count3
amount4 count4
amount5 count5
...
Then, HAVING is applied to that. So, let's say that amount1 is > .09 and amount4 is > .09; the result becomes
count1
count4
which is the result you query returns.
If you want to get the count of rows whose Amount is > .09, you should
SELECT COUNT(*) AS Expr1
FROM Current
WHERE Amount > .09
Eslam Afifi
|
|
|
|
|
Hey Guys,
I have a table similar to the follwing structure:
AID BID
1 1
2 1
3 2
I need to get all AIDs where count(AID)>1 when grouping by BID
so in the above sample, query will only return AIDs 1 and 2 but will not return 3 because only one AID is in BID 2.
Your help is appreciated.
Modified:
is there a better sollution other then using:
AND EXISTS (
SELECT COUNT(AID)
FROM TableA
GROUP BY BID
HAVING COUNT(AID) >1
)
|
|
|
|
|
I don't know if this is better or not.
SELECT AID FROM TableA WHERE BID IN
(SELECT BID
FROM TableA
GROUP BY BID HAVING Count(AID) > 1);
Eslam Afifi
|
|
|
|
|
|
Glad to help.
Eslam Afifi
|
|
|
|
|
tblVisitor is a list of visitors that is linked to a record in the tblEmployee table and what I need to do is select the last visitor based on the ClockedIn column, which is a DateTime.
So my question is how do I insert this :-
SELECT TOP 1 * FROM tblVisitors WHERE intEmpID = tblEmployee.intEmpID ORDER BY ClockedIn DESC
into this SELECT statement :-
SELECT TOP (100) PERCENT employee.intEmpID AS ID, employee.strEmpID AS CardReference,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Employee.strTitle + ' ' + Employee.strForename + ' ' + Employee.strSurname
END AS Visitor,
Visitor.Company,
Visitor.VisitorCardEmpId AS ResponsiblePersonnel,
CASE Employee.intStatus
WHEN 1 THEN
-1
ELSE
0
END AS ClockedIn,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Visitor.TimeIn
END As ArrivalTime
FROM tblEmployee AS employee LEFT OUTER JOIN
dbo.tblVisitors AS Visitor ON Visitor.intEmpId = employee.intEmpID
WHERE (employee.strEmpID LIKE 'V%')
ORDER BY CardReference
So that the result is one row pre employee.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Not sure I quite understand, but how about something like
.... Where Clockedin = (select max(clockedin) from tblvisitors)
i.e. you are selecting the latest clocked in time and using that to match back to your visitor.
BTW, why the SELECT TOP (100) PERCENT .. , surely thats just selecting them all, but causing the server extra work?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Bob,
Thanks very much, you pointed me in the right direction. I did n't spot the SELECT TOP (100) percent , which was added by the View designer.
My final solution if anyone is interested :-
SELECT DISTINCT employee.intEmpID AS ID, employee.strEmpID AS CardReference,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Employee.strTitle + ' ' + Employee.strForename + ' ' + Employee.strSurname
END AS Visitor,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 Company FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As Company,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As ResponsiblePersonnelID,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
dbo.fnGetEmployeeName((SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC))
END As ResponsiblePersonnel,
CASE Employee.intStatus
WHEN 1 THEN
-1
ELSE
0
END AS ClockedIn,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 TimeIn FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As ArrivalTime
FROM Tensor.dbo.tblEmployee AS employee LEFT OUTER JOIN
dbo.tblVisitors AS Visitor ON Visitor.intEmpId = employee.intEmpID
WHERE (employee.strEmpID LIKE 'V%')
ORDER BY CardReference
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Pleased to have been of use.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Good morning,
I have a question regarding if something is possible to do in SQL. Is it possible to setup a Linked Server in SQL, or some other SQL connection that will access an existing Web Service? I have found various web pages about setting up SQL data to be accessable as a Web Service, but I found nothing that says if it's possible for SQL to access a Web Service to pull back records as part of a stored procedure.
The reason I'm wondering about this is because as we are moving to a new database engine, it has been suggested to turn off access to Linked Servers.
David
|
|
|
|
|
I my be wrong, but I think you will need to write a .NET extension for SQL Server, which can then subscribe to a Web Service
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
I have a need to return a table of data, for a MS SQL Server 2005 Stored Procedure, based on the following :-
ID = Table1.ID
If Table1.Status = 0 Then
Name = Table1.Name
Company = NULL
Else
Name = Table2.Name
Company = Table2.Company
Where Table2.ID = Table1.ID
End
I hope this mud is clear.
My T-SQL is not bad for the run-of-mill type tasks, but I do struggle with the more complex ones. (But I am learning)
Any pointers on where I should start would be great.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|