Need SQL guru help

[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.PenID, Tbl1.StartWt, Tbl1.StartCV, Tbl1.Sex
FROM Tbl1
GROUP BY Tbl1.PenID, Tbl1.StartWt, Tbl1.StartCV, Tbl1.Sex
ORDER BY Tbl1.PenID
PIVOT Tbl1.WeekNo;

The output of the above query looks like this:

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

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

 

[Michel Walsh]

By "SQL Pivot" you mean "MS SQL Server 2007 - Pivot construction" ? If so,
you cannot. MS SQL Server Pivot requires a pre-determine fix set of values
(like using Columns Headings in Jet): you need an IN clause in the PIVOT.
So, unless you explicitly supply the exhaustive list of 37 integers (0 to
36)...



Vanderghast, Access MVP

 

[Guest]

Michel, thanks for your answer. I was afraid of that.

Do you know of any other way to attack this problem?

 

[Michel Walsh]

If you are using MS SQL Server and if the dba allows you to use a linked
server (to Jet; a little bit like Access allows linked tables, MS SQL Server
allows linked server) add a linked server be the Access database, through
sp_addlinkedserver, and ask it, to Jet that is, to produce the crosstab,
collecting the data it returned back to MS MSQL Server.

That solution may have security problems, though.

Sure, if the data is originally in MS SQL Server, NOT in Access, you may
consider using a dot-mdb file with a linked table to the data! Note that
this solution was initially proposed by Mary Chipman.


Another (maybe simpler) solution would be to generate those 37 new fields,
all the time.


Vanderghast, Access MVP

 

[Michel Walsh]

If you want to do it with all the 37 columns always displayed, using MS SQL
Server 2007 PIVOT, that could be something like this:

Note that MS SQL Server 2007 Pivot has NO group by clause, making it
quite... useless.


SELECT penID, startWt, StartCV, Sex, AVG( [0] ) AS week0, AVG( [1] ) AS
week1, ..., AVG( [36] ) AS week36
FROM
(
SELECT penID, startWt, StartCV, Sex, [0], [1], ..., [36]
FROM tbl1
PIVOT (
SUM(CurrWeight/population) FOR weekno IN ([0], [1], ...,
[36])
) AS pvt
) AS x
GROUP BY penID, startWt, StartCV, Sex




The inner most select clause does nothing, really, except generating new
columns for EACH EXISTING ROW. You can use MAX, MIN, or SUM (as I did here),
it is almost meaningless, as long as you don't use COUNT, since it operates
on ONE row at a time. The sub-query cannot return the pivot column, here
weekno, neither any aggregated column (for some obscure reason, if you ask
me), but can return any other column. The inner most query has no GROUP BY
and cannot have one (which is really weird, imho, but that is the way it is,
and which makes the aggregate you use... almost useless!).

Once the columns are generated, the outer most query can do the real job
(that a Jet-crosstab really does). But here, we simply average each new
fields, and make the appropriate groups.


I cannot test your query, but I did it over pubs:

SELECT stor_id, SUM(net30) AS qty30, SUM(net60) AS qty60
FROM
(SELECT stor_id, [Net 30] AS net30, [Net 60] AS net60
FROM sales
PIVOT( SUM(qty) FOR payterms IN([Net 30], [Net 60]) ) as pvt
) as x
GROUP BY stor_id

which returned

6380 NULL 8
7066 50 NULL
7067 80 10
7131 45 85
7896 NULL 25
8042 55 NULL



Vanderghast, Access MVP

 

[Guest]

Thanks again. I may have to make this work since I cannot come up with a
more elegant solution.