Классика баз данных - статьи
SELECT idTabel, idPaymentType, idPerson, Hours,
[26] AS h26, [27] AS h27, [28] AS h28, [29] AS h29, [30] AS h30, [31] AS h31
FROM
( SELECT idTabel, idPaymentType, idPerson, Hours, DAY(Date) as Day
FROM tblTabelFact) AS t1
PIVOT (SUM(Hours) FOR [Day] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
[21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])) AS t2
SELECT TabelRow.idPaymentType, Calendar.Date, TabelRow.idPerson,
TabelRow.idTabel, TabelRow.h1 AS Hours
FROM Tabel
INNER JOIN TabelRow ON Tabel.idTabel = TabelRow.idTabel
INNER JOIN Calendar ON YEAR(Calendar.Date) = YEAR(Tabel.Date)
AND MONTH(Calendar.Date) = MONTH(Tabel.Date) AND Calendar.Day = 1
UNION
SELECT TabelRow.idPaymentType, Calendar.Date, TabelRow.idPerson,
TabelRow.idTabel, TabelRow.h2 AS Hours
FROM Tabel
INNER JOIN TabelRow ON Tabel.idTabel = TabelRow.idTabel
INNER JOIN Calendar ON YEAR(Calendar.Date) = YEAR(Tabel.Date)
AND MONTH(Calendar.Date) = MONTH(Tabel.Date) AND Calendar.Day = 2
UNION
...
UNION
SELECT TabelRow.idPaymentType, Calendar.Date, TabelRow.idPerson,
TabelRow.idTabel, TabelRow.h31 AS Hours
FROM Tabel
INNER JOIN TabelRow ON Tabel.idTabel = TabelRow.idTabel
INNER JOIN Calendar ON YEAR(Calendar.Date) = YEAR(Tabel.Date)
AND MONTH(Calendar.Date) = MONTH(Tabel.Date) AND Calendar.Day = 31
для MS SQL-Server 2005:
SELECT t.idTabel, t.idPaymentType, idPerson, Hours, Calendar.Date
FROM tblTabelRow
UNPIVOT (Hours For Day IN (h1, h2, h3, h4, h5, h6, h7, h8, h9, h10,
h11, h12, h13, h14, h15, h16, h17, h18, h19, h20,
h21, h22, h23, h24, h25, h26, h27, h28, h29, h30, h31)) AS t
INNER JOIN tblTabel ON t.idTabel = tblTabel.idTabel
INNER JOIN Calendar ON YEAR(tblTabel.Date) = Calendar.YEAR
