Combining Multiple rows into 1 row x 1 column

I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?

Employee Name John Mike Dale

There are a number of ways to complete what you wish. Some features that you can take advantage of include:

select with CASE and MAX

User defined functions

SELECT with FOR XML syntax (better in SQL 2005 than SQL 2000)

PIVOT

Transact SQL SELECT extensions|||Very Cool, Thanks.

|||

Just for the sake of completeness, this can also be achieved via cursors:

Assuming #Employee temp table contains the data.

declare @.sql varchar(200), @.k int

set @.sql = ''

set @.k = 0

declare @.EmpName varchar(50)

declare abc cursor for select EmployeeName from #Employee

open abc

fetch next from abc into @.EmpName

while @.@.FETCH_STATUS = 0

begin

if @.k > 0 set @.SQL = @.SQL +', '

set @.SQL = @.SQL + @.EmpName

set @.k = @.k +1

fetch next from abc into @.EmpName

end

close abc

deallocate abc

SELECT @.SQL as Employees

Drop Table #Employee

|||

Code Snippet

declare @.Output varchar(max)

select @.Output = isnull(@.Output + ', ' + [Employee Name] , [Employee Name] )

from MyTable

select @.Output as [OneColumn]

Combining a Line Graph and Scatter Graph

I have to make a report of a combination of salary trends of
employees. The idea is to show the salary of a employee for a certain
period of time, also showing where he stands when he compared to
salaries of other employees.
I will show the salary of the employee in a line Graph, thats fine I
have already done it. X axis -> Employment Time, Y-axis Salary The
qquery will be like
userid, date, salary
72, 01/01/2001, 1000
72, 06/01/2001, 1600
72, 01/01/2002, 6000
72, 06/01/2002, 7080
72, 01/01/2003, 8010
72, 06/01/2003, 10000
72, 01/01/2004, 10050
72, 06/01/2004, 15500
salary in the Values section
date in the Category Section
The Line Graph is done.
Assuming I have the same query with more userid values (For other
employees also). I have to plot scatter points around the Line Graph
showing the salaries of each other employee as against the employee
72.
So the Graph will have 1 line for userid 72, scatter points around
that line.
How can I do it. Working Ideas please.
Thanks !
Anand Sagar
Now I have to show scatter pointsanybody there !?

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Column Value Lookup

What is the best way to lookup a value in a column from another data set?
I am trying to locate and display the employee name based on the employee id
found in another data set.
I.E.
13 = Terry Ward
14 = Peter Jackson
Thank you.I might be wrong, but I don't think you can do that, unfortunately.
You need to make the EmployeeID a parameter that you can use to query your
employee database table for names.
What are you trying to do? Might be a different way of doing it.
Kaisa M. Lindahl Lervik
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> What is the best way to lookup a value in a column from another data set?
> I am trying to locate and display the employee name based on the employee
> id
> found in another data set.
> I.E.
> 13 = Terry Ward
> 14 = Peter Jackson
> Thank you.|||In the Task table, employees are identified as numeric values. References to
the actual employee names are found in the Employee table.
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To =dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
"Kaisa M. Lindahl Lervik" wrote:
> I might be wrong, but I don't think you can do that, unfortunately.
> You need to make the EmployeeID a parameter that you can use to query your
> employee database table for names.
> What are you trying to do? Might be a different way of doing it.
> Kaisa M. Lindahl Lervik
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > What is the best way to lookup a value in a column from another data set?
> >
> > I am trying to locate and display the employee name based on the employee
> > id
> > found in another data set.
> >
> > I.E.
> > 13 = Terry Ward
> > 14 = Peter Jackson
> >
> > Thank you.
>
>|||Can you join the Task table to the emloyee table similar to the way the
tblIssue table is joined to the tblEmployee table is below to return the
employee name?
e.g. Something like this...
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Terry" wrote:
> In the Task table, employees are identified as numeric values. References to
> the actual employee names are found in the Employee table.
> SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> dbo.tblIssue.Opened_By,
> dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> dbo.tblIssue.Closed_Date
> FROM dbo.tblIssue
> INNER JOIN
> dbo.tblEmployee ON dbo.tblIssue.Assigned_To => dbo.tblEmployee.Employee_ID
> INNER JOIN
> dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> "Kaisa M. Lindahl Lervik" wrote:
> > I might be wrong, but I don't think you can do that, unfortunately.
> > You need to make the EmployeeID a parameter that you can use to query your
> > employee database table for names.
> >
> > What are you trying to do? Might be a different way of doing it.
> >
> > Kaisa M. Lindahl Lervik
> > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > What is the best way to lookup a value in a column from another data set?
> > >
> > > I am trying to locate and display the employee name based on the employee
> > > id
> > > found in another data set.
> > >
> > > I.E.
> > > 13 = Terry Ward
> > > 14 = Peter Jackson
> > >
> > > Thank you.
> >
> >
> >|||Do I need to create a new dataset and include the employee name in the report
in order to obtain the employee name along with the existing dataset called
IT_Projects?
DATASET 1:
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To = dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
DATASET 2:
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Matt" wrote:
> Can you join the Task table to the emloyee table similar to the way the
> tblIssue table is joined to the tblEmployee table is below to return the
> employee name?
> e.g. Something like this...
> SELECT dbo.tblEmployee.Name
> FROM tblTask
> JOIN tblEmployee
> ON tblTask.Employee_ID = tblEmployee.Employee_ID
> "Terry" wrote:
> > In the Task table, employees are identified as numeric values. References to
> > the actual employee names are found in the Employee table.
> >
> > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > dbo.tblIssue.Opened_By,
> > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > dbo.tblIssue.Closed_Date
> > FROM dbo.tblIssue
> > INNER JOIN
> > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > dbo.tblEmployee.Employee_ID
> > INNER JOIN
> > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> > > I might be wrong, but I don't think you can do that, unfortunately.
> > > You need to make the EmployeeID a parameter that you can use to query your
> > > employee database table for names.
> > >
> > > What are you trying to do? Might be a different way of doing it.
> > >
> > > Kaisa M. Lindahl Lervik
> > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > What is the best way to lookup a value in a column from another data set?
> > > >
> > > > I am trying to locate and display the employee name based on the employee
> > > > id
> > > > found in another data set.
> > > >
> > > > I.E.
> > > > 13 = Terry Ward
> > > > 14 = Peter Jackson
> > > >
> > > > Thank you.
> > >
> > >
> > >|||Ok, it sounds you are"...trying to locate and display the employee name based
on the employee id found in another data set."
So, it sounds like you have 1 dataset that does not return the employee name
and another dataset that does return the employee name. Instead of trying to
perform a lookup between the 2 datasets that you have, the best thing to do
would be to modify the query that currently does not include the employee
name to include the employee name in the select statement. Does that make
more sense?
If you want to post the 2 dataset queries that you have, that may help me
understand your situation better.
"Terry" wrote:
> Do I need to create a new dataset and include the employee name in the report
> in order to obtain the employee name along with the existing dataset called
> IT_Projects?
> DATASET 1:
> SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> dbo.tblIssue.Opened_By,
> dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> dbo.tblIssue.Closed_Date
> FROM dbo.tblIssue
> INNER JOIN
> dbo.tblEmployee ON dbo.tblIssue.Assigned_To => dbo.tblEmployee.Employee_ID
> INNER JOIN
> dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> DATASET 2:
> SELECT dbo.tblEmployee.Name
> FROM tblTask
> JOIN tblEmployee
> ON tblTask.Employee_ID = tblEmployee.Employee_ID
> "Matt" wrote:
> > Can you join the Task table to the emloyee table similar to the way the
> > tblIssue table is joined to the tblEmployee table is below to return the
> > employee name?
> >
> > e.g. Something like this...
> > SELECT dbo.tblEmployee.Name
> > FROM tblTask
> > JOIN tblEmployee
> > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> >
> > "Terry" wrote:
> >
> > > In the Task table, employees are identified as numeric values. References to
> > > the actual employee names are found in the Employee table.
> > >
> > > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > > dbo.tblIssue.Opened_By,
> > > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > > dbo.tblIssue.Closed_Date
> > > FROM dbo.tblIssue
> > > INNER JOIN
> > > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > > dbo.tblEmployee.Employee_ID
> > > INNER JOIN
> > > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> > >
> > > "Kaisa M. Lindahl Lervik" wrote:
> > >
> > > > I might be wrong, but I don't think you can do that, unfortunately.
> > > > You need to make the EmployeeID a parameter that you can use to query your
> > > > employee database table for names.
> > > >
> > > > What are you trying to do? Might be a different way of doing it.
> > > >
> > > > Kaisa M. Lindahl Lervik
> > > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > > What is the best way to lookup a value in a column from another data set?
> > > > >
> > > > > I am trying to locate and display the employee name based on the employee
> > > > > id
> > > > > found in another data set.
> > > > >
> > > > > I.E.
> > > > > 13 = Terry Ward
> > > > > 14 = Peter Jackson
> > > > >
> > > > > Thank you.
> > > >
> > > >
> > > >|||Thank you for your speedy response.
However, please review the following 2 dataset queries being used.
How can I include the employee name without causes JOIN conflicts?
DATASET 1:
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To = dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
DATASET 2:
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Matt" wrote:
> Ok, it sounds you are"...trying to locate and display the employee name based
> on the employee id found in another data set."
> So, it sounds like you have 1 dataset that does not return the employee name
> and another dataset that does return the employee name. Instead of trying to
> perform a lookup between the 2 datasets that you have, the best thing to do
> would be to modify the query that currently does not include the employee
> name to include the employee name in the select statement. Does that make
> more sense?
> If you want to post the 2 dataset queries that you have, that may help me
> understand your situation better.
> "Terry" wrote:
> > Do I need to create a new dataset and include the employee name in the report
> > in order to obtain the employee name along with the existing dataset called
> > IT_Projects?
> >
> > DATASET 1:
> >
> > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > dbo.tblIssue.Opened_By,
> > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > dbo.tblIssue.Closed_Date
> > FROM dbo.tblIssue
> > INNER JOIN
> > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > dbo.tblEmployee.Employee_ID
> > INNER JOIN
> > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> >
> > DATASET 2:
> >
> > SELECT dbo.tblEmployee.Name
> > FROM tblTask
> > JOIN tblEmployee
> > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> >
> > "Matt" wrote:
> >
> > > Can you join the Task table to the emloyee table similar to the way the
> > > tblIssue table is joined to the tblEmployee table is below to return the
> > > employee name?
> > >
> > > e.g. Something like this...
> > > SELECT dbo.tblEmployee.Name
> > > FROM tblTask
> > > JOIN tblEmployee
> > > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> > >
> > > "Terry" wrote:
> > >
> > > > In the Task table, employees are identified as numeric values. References to
> > > > the actual employee names are found in the Employee table.
> > > >
> > > > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > > > dbo.tblIssue.Opened_By,
> > > > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > > > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > > > dbo.tblIssue.Closed_Date
> > > > FROM dbo.tblIssue
> > > > INNER JOIN
> > > > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > > > dbo.tblEmployee.Employee_ID
> > > > INNER JOIN
> > > > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> > > >
> > > > "Kaisa M. Lindahl Lervik" wrote:
> > > >
> > > > > I might be wrong, but I don't think you can do that, unfortunately.
> > > > > You need to make the EmployeeID a parameter that you can use to query your
> > > > > employee database table for names.
> > > > >
> > > > > What are you trying to do? Might be a different way of doing it.
> > > > >
> > > > > Kaisa M. Lindahl Lervik
> > > > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > > > What is the best way to lookup a value in a column from another data set?
> > > > > >
> > > > > > I am trying to locate and display the employee name based on the employee
> > > > > > id
> > > > > > found in another data set.
> > > > > >
> > > > > > I.E.
> > > > > > 13 = Terry Ward
> > > > > > 14 = Peter Jackson
> > > > > >
> > > > > > Thank you.
> > > > >
> > > > >
> > > > >

Column Comparison

I'm in need of a sql query that I'm not sure is possible. Here is an
example of how it's laid out.

employee ID Job class Last Change Date
12345 x 2/1/2004
12345 y 1/15/2004
12345 z 1/1/2004

We know that this person is in job class 'x' because it's the most
recent change. Is there a way to write a query that will exclude the
lines 'y' and 'z' because they are currently incorrect?

I would appreciate any help I could get. ThanksMatt (mjreiter@.yahoo.com) writes:
> I'm in need of a sql query that I'm not sure is possible. Here is an
> example of how it's laid out.
>
> employee ID Job class Last Change Date
> 12345 x 2/1/2004
> 12345 y 1/15/2004
> 12345 z 1/1/2004
> We know that this person is in job class 'x' because it's the most
> recent change. Is there a way to write a query that will exclude the
> lines 'y' and 'z' because they are currently incorrect?

SELECT t.empolyeeid, t.job_class, t.lastchangedate
FROM tbl t
JOIN (SELECT employeeid, lastchangedate = MAX(lastchangedate)
FROM tbl
GROUP BY employeeid) AS x
ON t.employeeid = x.employeeid
AND t.lastchangedate = m.lastchangdate

What you see in the middle is a derived table. This a very powerful
feature in SQL. It is sort of a temporary table in the middle of the
query, but it is not matierialized as such, and the optimizer may find
shortcuts so that the entire table is never computed, only what is
needed for the query.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

column as result

Hello all,
I have this procedure
declare @.column varchar(200)
declare @.sql varchar(200)
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
SET @.sql ='select * from '+@.column
EXEC (@.sql)
I have this mistake:
Invalid object name '<@.column>'
Could someone tell me why?
Inatry to run these and find the result
declare @.column varchar(200)
declare @.sql varchar(200)
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
and see if you have a table or view by that result name.|||Are you sure the expected dynamic statement is valid. i.e does the @.column
value pass a valid object name .
What valie is returened from this statement ?
SELECT @.column = 'sysdatabases' FROM sysdatabases WHERE dbid = '8'
I just ran the following as a test , and it worked fine .
declare @.column varchar (100)
declare @.sql varchar(200)
SELECT @.column = 'sysdatabases' FROM sysdatabases WHERE dbid = '8'
SET @.sql ='select * from ' + @.column
EXEC (@.sql)
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1144079096.559619.115510@.j33g2000cwa.googlegroups.com...
> Hello all,
> I have this procedure
> declare @.column varchar(200)
> declare @.sql varchar(200)
> SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
> SET @.sql ='select * from '+@.column
> EXEC (@.sql)
> I have this mistake:
> Invalid object name '<@.column>'
> Could someone tell me why?
> Ina
>|||Step through it:
/*Declare varialbes */
declare @.column varchar(200)
declare @.sql varchar(200)
/*The statement below sets the value of @.column to the (hopefully one)
value of lastname for the employee with an ID_employee value of 3 */
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
/*Suppose that the value stored in @.column is now 'williams' */
SET @.sql ='select * from '+ @.column
/* The above evaluates to 'select * from williams', and 'williams' is
probably
not an object in your database. You could use the PRINT statement to
see what you would be EXECing */
PRINT @.sql
EXEC (@.sql)
"ina" wrote:

> Hello all,
> I have this procedure
> declare @.column varchar(200)
> declare @.sql varchar(200)
> SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
> SET @.sql ='select * from '+@.column
> EXEC (@.sql)
> I have this mistake:
> Invalid object name '<@.column>'
> Could someone tell me why?
> Ina
>|||yes thank you I have the result I can see all the table but if :
declare @.column varchar(200)
declare @.sql varchar(200)
SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
SET @.sql ='select firstname from employee where lastname='+@.column
EXEC (@.sql)
but I have this error now: Invalid column name 'pittet' (it is the last
name of an employee)
@.column needs to give me the last name of the employee ID number 3 and
sql need to give me the first name of the employee which lastname is
pittet.
ina|||SET @.sql ='select firstname from employee where lastname=''' + @.column +
''''
EXEC (@.sql)
p|||On 3 Apr 2006 09:23:44 -0700, ina wrote:

>yes thank you I have the result I can see all the table but if :
>declare @.column varchar(200)
>declare @.sql varchar(200)
>SELECT @.column = lastname FROM Employee WHERE ID_employee = "3"
>SET @.sql ='select firstname from employee where lastname='+@.column
>EXEC (@.sql)
>but I have this error now: Invalid column name 'pittet' (it is the last
>name of an employee)
>@.column needs to give me the last name of the employee ID number 3 and
>sql need to give me the first name of the employee which lastname is
>pittet.
Hi Ina,
I see that Rogas69 already posted a reply to show the error in your
code. But he or she didn't address your bigger error - why are you even
using dynamic SQL here? Why not do it in a single query?
If the requirement is to show the first name of every employee who has
the same last name as employee 3, then you could use
SELECT firstname
FROM employee
WHERE lastname = (SELECT lastname
FROM employee
WHERE ID_employee = '3')
And if that was not your requirement, then the code you posted (with the
correction posted by Rogas69) won't produce the required results.
Hugo Kornelis, SQL Server MVP|||Thank you all for these answers :)
I'd prefer to use dynamic SQL because I would like to understand how to
declare variable in a sql code; with your help I could understand more.
:)|||On 3 Apr 2006 23:57:03 -0700, ina wrote:

>Thank you all for these answers :)
>I'd prefer to use dynamic SQL because I would like to understand how to
>declare variable in a sql code; with your help I could understand more.
Hi Ina,
Okay. Just make sure that you read (and understand) everything on this
page: http://www.sommarskog.se/dynamic_sql.html.
Hugo Kornelis, SQL Server MVP