Separating values to multiple fields 2

[reg999]

OK... This has got to be simple.
I have a table laid out as follows.

Date G S TN SN GN
4/1/03 4.33 48 1 1 H
4/1/03 3.28 41 2 1 H
4/1/03 4.4 40 3 1 H
4/1/03 6.25 38 1 2 H
4/1/03 3.87 44 2 2 H
4/1/03 2.28 33 3 2 H
4/1/03 4.82 47 1 1 L
4/1/03 5.37 46 2 1 L
4/1/03 3.88 37 3 1 L

I want a query to have the following result.
As shown: Based on each date, SN, and GN value: (G1, G2, G3, etc. determined by TN)

Date G1 G2 G3 S1 S2 S3 SN GN
4/1/03 4.33 3.28 4.4 48 41 40 1 H
4/1/03 6.25 3.87 2.28 38 44 33 2 H
4/1/03 4.82 5.37 3.88 47 46 37 1 L

How do I query the table? Also, If the situation were reversed, how would it be queried?

 

[rac2]

Create three views (Queries) from the basic table. Each view will show just the rows for a particular TN.

SELECT Date,G AS "G1",S AS "S1",TN,SN,GN
FROM MyTable
WHERE TN = 1

Give them names like MyTN1s.

Create a fourth view by JOINing the three on SN and GN, or on any other data that allows you to match up the rows.

SELECT a.Date, G1,G2,G3,S1,S2,S3, a.SN, a.GN
FROM MyTN1s a
JOIN MyTN2s b ON b.SN = a.SN
            AND  b.GN = b.GN
JOIN MyTN3s c ON c.SN = a.SN
            AND  c.GN = b.GN

 

[PHV]

SELECT A.Date,A.G AS G1,B.G AS G2,C.G AS G3,A.S AS S1,B.S AS S2,C.S AS S3,A.SN,A.GN
FROM (yourTable AS A
INNER JOIN yourTable AS B ON A.Date=B.Date AND A.SN=B.SN AND A.GN=B.GN)
INNER JOIN yourTable AS C ON A.Date=C.Date AND A.SN=C.SN AND A.GN=C.GN
WHERE A.TN=1 AND B.TN=2 AND C.TN=3;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[reg999]

Thank you both.
As usual, works like a dream... I'll make good use of this.
Reg