How can I use PIVOT to show simultationly average and count in its cells?

Yes you need to use the old style cross tab for this. The PIVOT is just syntactic sugar that resolves to pretty much the same approach.

SELECT AVG(CASE WHEN col='foo' THEN col END) AS AvgFoo,       COUNT(CASE WHEN col='foo' THEN col END) AS CountFoo,...

If you have many aggregates you could always use a CTE

WITH cte As(SELECT CASE WHEN col='foo' THEN col END AS Foo...)SELECT MAX(Foo),MIN(Foo), COUNT(Foo), STDEV(Foo)FROM cte

Simultaneous.. in its cells. So you mean within the same cell, therefore as a varchar?

You could calc the avg and count values in an aggregate query before using the pivot, and concatenate them together as text.

The role of the PIVOT operator here would only be to transform rows to columns, and some aggregate function (e.g. MAX/MIN) would be used only because it is required by the syntax - your pre-calculated aggregate query would only have one value per pivoted column.

EDIT

Following bernd_k's oracle/mssql solution, I would like to point out another way to do this in SQL Server. It requires streamlining the multiple columns into a single column.

SELECT MODULE,  modus + '_' + case which when 1 then 'AVG' else 'COUNT' end AS modus,  case which when 1 then AVG(duration) else COUNT(duration) end AS valueFROM test_data, (select 1 as which union all select 2) xGROUP BY MODULE, modus, whichSELECT *FROM ( SELECT MODULE,  modus + '_' + case which when 1 then 'AVG' else 'COUNT' end AS modus,  case which when 1 then CAST(AVG(1.0*duration) AS NUMERIC(10,2)) else COUNT(duration) end AS value FROM test_data, (select 1 as which union all select 2) x GROUP BY MODULE, modus, which) PPIVOT (MAX(value) FOR modus in ([A_AVG], [A_COUNT], [B_AVG], [B_COUNT])) AS pvtORDER BY pvt.MODULE

In the example above, AVG and COUNT are compatible (count - int => numeric). If they are not, convert both explicitly to a compatible type.

Note - The first query shows AVG for M2/A as 2, due to integer averaging. The 2nd (pivoted) query shows the actual average taking into account decimals.

Solution for Oracle 11g + :

create table test_data (    module varchar2(30),    modus   varchar2(30),    duration Number(10));insert into test_data values ('M1', 'A', 5); insert into test_data values ('M1', 'A', 5); insert into test_data values ('M1', 'B', 3); insert into test_data values ('M2', 'A', 1); insert into test_data values ('M2', 'A', 4); select   *FROM (select   *from test_data) PIVOT (    AVG(duration) avg , count(duration) count    FOR modus in ( 'A', 'B')) pvtORDER BY pvt.module;

I do not like the column names containing apostrophes, but the result contains what I want:

MODULE                            'A'_AVG  'A'_COUNT    'B'_AVG  'B'_COUNT------------------------------ ---------- ---------- ---------- ----------M1                                      5          2          3          1M2                                    2.5          2                     0

I really wonder what the Microsoft boys did, when they only allowed one aggregate function within pivot. I call evaluation avgs without accompanying counts statistical lies.

SQL-Server 2005 + (based on Cyberwiki):

CREATE TABLE test_data (    MODULE VARCHAR(30),    modus   VARCHAR(30),    duration INTEGER);INSERT INTO test_data VALUES ('M1', 'A', 5); INSERT INTO test_data VALUES ('M1', 'A', 5); INSERT INTO test_data VALUES ('M1', 'B', 3); INSERT INTO test_data VALUES ('M2', 'A', 1); INSERT INTO test_data VALUES ('M2', 'A', 4); SELECT MODULE, modus, ISNULL(LTRIM(STR(AVG(duration))), '') + '|' + ISNULL(LTRIM(STR(COUNT(duration))), '') RESULTFROM test_dataGROUP BY MODULE, modus;SELECT   *FROM (SELECT MODULE, modus, ISNULL(LTRIM(STR(AVG(duration))), '') + '|' + ISNULL(LTRIM(STR(COUNT(duration))), '') RESULTFROM test_dataGROUP BY MODULE, modus) TPIVOT ( MAX(RESULT) FOR modus in ( [A], [B])) AS pvtORDER BY pvt.MODULE

result:

MODULE                         A                     B------------------------------ --------------------- ---------------------M1                             5|2                   3|1M2                             2|2                   NULL