June 21, 2002 at 4:49 am
My table consists of three fields as below
Emp-Code Date Time
(Integer) (SmallDatetime) (SmallDatetime)
1 21/02/2002 9.30 AM
2 21/02/2002 9.31 AM
1 22/02/2002 9.00 AM
2 22/02/2002 9.01 AM
1 23/02/2002 9.00 AM
2 23/02/2002 9.01 AM
....
New I want to generate a cross tab view
as below
Emp-code 21/02/2002 22/02/2002 23/02/2002
1 9:31 AM 9:01 AM 9:01 AM
2 9:31 AM 9:01 AM 9:01 AM
.
.
.
.
Please send me script for the above
Awaiting for reply
Thanking You
Krishna Kumar
June 21, 2002 at 10:29 am
Not easy. You'd have to build a SQL join for each column you need. USually this is done with dynamic SQL as
select emp.emp-code, b.time as b.date, c.time as c.date
from emp
inner join emp b
on emp.emp_code = b.emp_code
and b.date = '21/02/2002'
inner join emp c
on emp.emp_code = c.emp_code
and c.date = '22/02/2002'
and so on.
You would probably use a cursor or loop of some sort to generate this sql in a varialbe and then do an exec() to run it.
As an FYI, this is best handled on the client, as there are better tools and more resources to handle something like this. Excel is a great client to do this with.
Steve Jones
June 22, 2002 at 6:52 am
Check this Article ,you can do it in Sql server too http://www.sqlteam.com/item.asp?ItemID=2955 .
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply