-


         

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

  • unpivot-, TabelFact :

    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

        





    Forekc.ru
    , , , , , , , , , ,