Инструкция TRANSFORM, примеры

Использованиепредложения SQL TRANSFORM для создания перекрестного запроса, демонстрирующего число заказов, принятых каждым сотрудником в каждом квартале 1994 г. Для выполнения данной процедуры требуется функция SQLTRANSFORMOutput.

Sub TransformX1()

Dim dbs As Database

Dim strSQL As String

Dim qdfTRANSFORM As QueryDef

strSQL = " PARAMETERS prmYear SHORT; TRANSFORM " _

& " Count(КодЗаказа) " _

& " SELECT Имя & " " " " & Фамилия AS " _

& " ФИО FROM Сотрудники INNER JOIN Заказы " _

& " ON Сотрудники.КодСотрудника = " _

& " Заказы.КодСотрудника WHERE DatePart " _

& " (" " yyyy" ", ДатаРазмещения) = [prmYear] "

strSQL = strSQL & " GROUP BY Имя & " _

& " " " " " & Фамилия " _

& " ORDER BY Имя & " " " " & Фамилия " _

& " PIVOT DatePart(" " q" ", ДатаРазмещения)"

' Укажите в следующей строке путь к базе данных " Борей"

' на вашем компьютере.

Set dbs = OpenDatabase(" Борей.mdb")

Set qdfTRANSFORM = dbs.CreateQueryDef _

(" ", strSQL)

SQLTRANSFORMOutput qdfTRANSFORM, 1994

dbs.Close

End Sub

В следующем примере предложение SQL TRANSFORM используется для создания более сложного перекрестного запроса, демонстрирующего общую сумму заказов, принятых каждым сотрудником в каждом квартале 1994 г. Для выполнения данной процедуры требуется функция SQLTRANSFORMOutput.

Sub TransformX2()

Dim dbs As Database

Dim strSQL As String

Dim qdfTRANSFORM As QueryDef

strSQL = " PARAMETERS prmYear SHORT; TRANSFORM " _

& " Sum(Всего) SELECT Имя & " " " " " _

& " & Фамилия AS ФИО " _

& " FROM Сотрудники INNER JOIN " _

& " (Заказы INNER JOIN [Сумма заказов] " _

& " ON Заказы.КодЗаказа = " _

& " [Сумма заказов].КодЗаказа) " _

& " ON Сотрудники.КодСотрудника = " _

& " Заказы.КодСотрудника WHERE DatePart" _

& " (" " yyyy" ", ДатаРазмещения) = [prmYear] "

strSQL = strSQL & " GROUP BY Имя & " " " " " _

& " & Фамилия " _

& " ORDER BY Имя & " " " " & Фамилия " _

& " PIVOT DatePart(" " q" ", ДатаРазмещения)"

' Укажите в следующей строке путь к базе данных " Борей"

' на вашем компьютере.

Set dbs = OpenDatabase(" Борей.mdb")

SQLTRANSFORMOutput qdfTRANSFORM, 1994

Set qdfTRANSFORM = dbs.CreateQueryDef _

(" ", strSQL)

dbs.Close

End Sub

Function SQLTRANSFORMOutput(qdfTemp As QueryDef, _

intYear As Integer)

Dim rstTRANSFORM As Recordset

Dim fldLoop As Field

Dim booFirst As Boolean

qdfTemp.PARAMETERS! prmYear = intYear

Set rstTRANSFORM = qdfTemp.OpenRecordset()

Debug.Print qdfTemp.SQL

Debug.Print

Debug.Print,, " Квартал"

With rstTRANSFORM

booFirst = True

For Each fldLoop In.Fields

If booFirst = True Then

Debug.Print fldLoop.Name

Debug.Print,;

booFirst = False

Else

Debug.Print, fldLoop.Name;

End If

Next fldLoop

Debug.Print

Do While Not.EOF

booFirst = True

For Each fldLoop In.Fields

If booFirst = True Then

Debug.Print fldLoop

Debug.Print,;

booFirst = False

Else

Debug.Print, fldLoop;

End If

Next fldLoop

Debug.Print

.MoveNext

Loop

End With

End Function

Значения, возвращенные аргументом поле, используются в качестве заголовков столбцов в результирующем наборе запроса. Например, продажи по месяцам в перекрестном запросе образуют результирующую таблицу из 12 столбцов. Аргумент поле можно ограничить, чтобы создать заголовки из фиксированных значений (значение_1, значение_2), перечисленных в необязательном предложении IN. Кроме того, фиксированные заголовки, которым не соответствуют реальные данные, можно использовать для создания дополнительных столбцов.

Совет. Существует возможность вывести данные в перекрестной таблице без создания в базе данных отдельного запроса. Для этого следует использовать мастер сводных таблиц. В сводной таблице пользователь имеет возможность изменять заголовкистрок или столбцов, что позволяет анализировать данные различными способами.