A Query-Transpose of Data

[Sameer]

I am supplying you with Sample Data:-

Initial Classcode SampleSize Average
------- ---------- -------------------------------
ADK SSC 22 3.6800000000000002
ADK TSC 17 2.7599999999999998
ADK TSM 5 3.5499999999999998
ANB FCA 31 3.23
ANB FCB 50 3.0499999999999998
ANB FCC 30 3.0899999999999999
ANB SCA 35 3.02
ANB SCB 9 3.4300000000000002
ANB TCA 30 2.77
ANB TCB 6 1.8799999999999999
APG MCH 10 3.8300000000000001
APG TSCH 9 4.21
AUG FCC 30 3.5499999999999998
AUG SCA 28 2.7800000000000002
AUG SCB 29 3.4300000000000002
AUG SCC 30 2.8999999999999999
AUG TCA 30 2.8599999999999999
AUG TCB 29 2.1200000000000001
AVK TSP 12 3.6200000000000001
BKK FS 32 2.52
BKK TSM 5 3.3799999999999999
BSK SSP 28 3.1200000000000001
BSK TSP 12 3.0600000000000001
------- ---------- -------------------------------

These are the averages of teachers grouped by initial.
Maximum 7 averages are related to each teacher.
Ignore the column SampleSize.
Using this output is it possible to get output like this:

Initial Class1 Avg1 Class2 Avg2 Class3 Avg3 Class4 Avg4..
ADK TSA 1.4 TSB 2.5 TSC 4.5 SSC 5.0..
ANB SSA 1.4 SSB 2.5 NULL NULL NULL NULL..
APG .........................
AVK ....................
BKK ....................
BSK .....................
Since the maximum class nos are 7, those having less than classes will

contain NULL in the class and average field.

Is it possible to carry out this in single query?

-Sameer

[Erland Sommarskog]

Sameer (sa******@gmail.com) writes:

These are the averages of teachers grouped by initial.
Maximum 7 averages are related to each teacher.
Ignore the column SampleSize.
Using this output is it possible to get output like this:

Initial Class1 Avg1 Class2 Avg2 Class3 Avg3 Class4 Avg4..
ADK TSA 1.4 TSB 2.5 TSC 4.5 SSC 5.0..
ANB SSA 1.4 SSB 2.5 NULL NULL NULL NULL..
APG .........................
AVK ....................
BKK ....................
BSK .....................
The averages in the output does not seem to agree with the averages
in the result. But there is no indication of any calculation.
Since the maximum class nos are 7, those having less than classes will
contain NULL in the class and average field.

Is it possible to carry out this in single query?

Single query may be possible, but I leave that to the purists. I would
use a temp table:

INSERT #temp (initial, class, average, average)
SELECT initial, class average, (SELECT COUNT(*)
FROM tbl b
WHERE a.class <= b.class)
FROM tbl

Then it a seven-way self-join:

SELECT a.initial, a.class, a.avg, b.class, b.avg, ...
FROM #temp a
LEFT JOIN #temp b ON a.initial = b.initial
AND b.classno = 2
LEFT JOIN #temp c ON a.initial = c.initial
AND c.classno = 2
...
WHERE a.classno = 1
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp