Convert Access CrossTab query to SQL Pivot?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Back
Top