HELP!!!urgent...who can help me..pls~

How to Write a select statement to return query Table A and to return in Table B format?

Table A

Name________Date________FieldType_______FieldValue
Tay Hui Boon__1/1/2005_______Title________Project Engineer
Tay Hui Boon__1/1/2005_____Department_____IT Department
Tay Hui Boon__1/1/2007_______Title_____Assist Project Manager
Ong Lay Keow__1/1/2006______Title_______Software Engineer
Ong Lay Keow__1/1/2006____Department_____IT Department

Table B

Date__________Name__________Title_________Departme nt
1/1/2007_____Tay Hui Boon____Assist Project Manager____IT Department
1/1/2006_____Ong Lay Keow_____Software Engineer______IT Department

Why would the order be important?
"SELECT [Date], [Name], Title, Department FROM tableA"

Are you using VB?
Date and Name are reserved words in VB and possibly in SQL, hence the brackets. Should rename your fields to something like DateHire and NameEmployee.

i m using jsp. u mean i jz select statement like "SELECT [Date], [Name], Title, Department FROM tableA", then can return in table B format??Or i nid to use relias to rename the fields???

Hopefully, this query works for you....


SELECT
A.Name
, A.Date
, (
SELECT TOP 1 FieldValue
FROM TabA t
WHERE t.FieldType = 'Title'
AND t.Name = A.Name
ORDER BY t.Date DESC
) AS Title
, (
SELECT TOP 1 FieldValue
FROM TabA d
WHERE d.FieldType = 'Department'
AND d.Name = A.Name
ORDER BY d.Date DESC
) As Department
FROM TabA A
WHERE A.Date = ( SELECT MAX(Date) FROM TabA WHERE Name = A.Name )

This is an SQL statement. I made some assumptions that you were familiar with SQL and opening a recordset with a SELECT statement and were using either VB or Access data tables. Would also have to establish connection to the tables and declare recordset variable.

Dim cn as ADODB.Connection
Set cn = CurrentProject.Connection 'or whatever connection is needed
Dim rs as ADODB.Recordset
Set rs = New ADODB.Connection

rs.Open "SELECT [Date], [Name], FieldValue As Title, FieldType As Department FROM tableA WHERE FieldType = 'IT Department' ORDER by [Date] DESC;", cn, adOpenStatic, adLockPessimistic

I don't know if jsp can recognize SQL. If it does, this may work.

I recommended changing the names in the actual table so you don't have to remember to use brackets in SQL statements.

Edit: Sorry, didn't notice the field alias situation, got stuck thinking you meant field order. Analyzing and composing online not always productive. Query lot more complicated than l initially thought. Believe Mualsh got it.

thx all...i think i gt the solution for tis question le...thank you vy much...

Hi, I hope the below SQL query will be useful for you


Note: test2 is a Table Name

Thanks & Regards
Sakthimeenakshi.S