Convert Access CrossTab query to SQL Pivot?

[Guest]

I've been working on this a while, but I know nothing about Pivots in SQL and
it just isn't clicking with me.

Can anyone convert this Access query to a SQL Pivot?

TRANSFORM Avg(Tbl1!CurrWeight/Tbl1!Population) AS Weight
SELECT Tbl1.RoomID, Tbl1.RepID, Tbl1.PenID, Tbl1.StartWt, Tbl1.StartCV,
Tbl1.Sex
FROM Tbl1
GROUP BY Tbl1.RoomID, Tbl1.RepID, Tbl1.PenID, Tbl1.StartWt, Tbl1.StartCV,
Tbl1.Sex
ORDER BY Tbl1.PenID
PIVOT Tbl1.WeekNo;

The output of the above query looks like this:

RoomID RepID PenID StartWt StartCV Sex 2 7 8
12
7 6 7-1 14.6868 18.1525 Mixed 22.24 60.50
126.65
7 3 7-10 16.7279 23.7587 Mixed 25.12 67.97
133.18
7 4 7-25 15.0412 16.0494 Mixed 22.94
74.00 130.58
7 5 7-3 15.9044 17.0957 Mixed 23.76 64.94
131.21

The WeekNo field is not a predefined set of numbers, it can be different
ranging anywhere from 0 to 36 depending on the trials.

Thanks in advance for any help on this.

RD

 

[Guest]

Since my output wrapped, it may be confusing. Here is the output with the
first two columns omitted so it shouldn't wrap:

PenID StartWt StartCV Sex 2 7 8 12
7-1 14.6868 18.1525 Mixed 22.24 60.50 126.65
7-10 16.7279 23.7587 Mixed 25.12 67.97 133.18
7-25 15.0412 16.0494 Mixed 22.94 74.00 130.58
7-3 15.9044 17.0957 Mixed 23.76 64.94 131.21