Freigeben über

  • Artikel

FROM (Transact-SQL)

Legt die Tabellen, Sichten, abgeleiteten Tabellen und verknüpften Tabellen in DELETE-, SELECT- und UPDATE-Anweisungen fest. Die FROM-Klausel wird in der SELECT-Anweisung immer benötigt, es sei denn, die Auswahlliste enthält nur Konstanten, Variablen und arithmetische Ausdrücke (keine Spaltennamen).

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

Syntax

[ FROM { <table_source> } [ ,...n ] ] 
<table_source> ::= 
{
        table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
        [ WITH ( < table_hint > [ [ , ]...n ] ) ] 
    | rowset_function [ [ AS ] table_alias ] 
        [ ( bulk_column_alias [ ,...n ] ) ] 
        | user_defined_function [ [ AS ] table_alias ] ]
    | OPENXML <openxml_clause> 
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
    | <joined_table> 
    | <pivoted_table> 
    | <unpivoted_table>
      | @variable [ [ AS ] table_alias ]
        | @variable.function_call ( expression [ ,...n ] ) [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
}
<tablesample_clause> ::=
    TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT | ROWS ] ) 
        [ REPEATABLE ( repeat_seed ) ] 

<joined_table> ::= 
{
    <table_source> <join_type> <table_source> ON <search_condition> 
    | <table_source> CROSS JOIN <table_source> 
    | left_table_source { CROSS | OUTER } APPLY right_table_source 
    | [ ( ] <joined_table> [ ) ] 
}
<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

<pivoted_table> ::=
        table_source PIVOT <pivot_clause> [ AS ] table_alias

<pivot_clause> ::=
        ( aggregate_function ( value_column [ [ , ]...n ]) 
        FOR pivot_column 
        IN ( <column_list> ) 
    ) 

<unpivoted_table> ::=
        table_source UNPIVOT <unpivot_clause> [ AS ] table_alias

<unpivot_clause> ::=
        ( value_column FOR pivot_column IN ( <column_list> ) ) 

<column_list> ::=
          column_name [ ,...n ]

Argumente

  • <table_source>
    Gibt eine Tabelle, Sicht, Tabellenvariable oder abgeleitete Tabelle als Quelle mit oder ohne Alias zum Verwenden in der Transact-SQL-Anweisung an. In einer Anweisung können bis zu 256 Tabellenquellen verwendet werden. Allerdings variiert das Limit in Abhängigkeit vom verfügbaren Arbeitsspeicher und der Komplexität anderer Ausdrücke in der Abfrage. Einzelne Abfragen unterstützen möglicherweise nicht bis zu 256 Tabellenquellen.

    HinweisHinweis

    Durch eine hohe Anzahl an Tabellen, auf die in einer Abfrage verwiesen wird, kann möglicherweise die Abfrageleistung beeinträchtigt werden. Zusätzliche Faktoren haben außerdem Auswirkungen auf die Kompilierungs- und Optimierungszeit. Dazu zählt das Vorhandensein von Indizes und indizierten Sichten für das <table_source>-Argument und die Größe des <select_list>-Arguments in der SELECT-Anweisung.

    Die Reihenfolge von Tabellenquellen nach dem FROM-Schlüsselwort hat keinen Einfluss auf das zurückgegebene Resultset. SQL Server gibt Fehler zurück, wenn die FROM-Klausel doppelte Namen enthält.

  • table_or_view_name
    Der Name einer Tabelle oder Sicht.

    Wenn die Tabelle oder Sicht in einer anderen Datenbank für die gleiche Instanz von SQL Server vorhanden ist, verwenden Sie einen vollqualifizierten Namen in der Form database.schema.object_name.

    Wenn die Tabelle oder die Sicht l außerhalb der Instanz von SQL Server1 vorhanden ist, verwenden Sie einen vierteiligen Namen in der Form linked_server.catalog.schema.object. Weitere Informationen finden Sie unter sp_addlinkedserver (Transact-SQL). Ein vierteiliger Name mit der OPENDATASOURCE-Funktion als Serverteil des Namens kann ebenfalls zum Angeben der Remotequelltabelle verwendet werden. Wenn OPENDATASOURCE angegeben wird, gelten database_name und schema_name möglicherweise nicht für alle Datenquellen und unterliegen den Funktionen des OLE DB-Anbieters, der auf das Remoteobjekt zugreift. Weitere Informationen finden Sie unter Verteilte Abfragen.

  • [AS] table_alias
    Ein Alias für table_source, der zur Vereinfachung oder zur Unterscheidung einer Tabelle oder Sicht in einer Selbstverknüpfung oder Unterabfrage verwendet werden kann. Ein Alias ist oftmals ein verkürzter Tabellenname, der verwendet wird, um in einer Verknüpfung auf bestimmte Spalten der beteiligten Tabellen zu verweisen. Falls ein Spaltenname in mehr als einer Tabelle der Verknüpfung vorkommt, muss dieser Spaltenname für SQL Server durch einen Tabellennamen, einen Sichtnamen oder einen Alias gekennzeichnet werden. Falls ein Alias definiert ist, kann der Tabellenname nicht verwendet werden.

    Wenn eine abgeleitete Tabelle, Rowsetwertfunktion, Tabellenwertfunktion oder Operatorklausel (z. B. PIVOT oder UNPIVOT) verwendet wird, ist der erforderliche table_alias-Ausdruck am Ende der Klausel der zurückgegebene verknüpfte Tabellenname für alle Spalten, einschließlich gruppierter Spalten.

  • WITH (<table_hint> )
    Gibt an, dass der Abfrageoptimierer eine Optimierungs- oder Sperrstrategie bei dieser Tabelle und für diese Anweisung verwendet. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

  • rowset_function
    Gibt eine der Rowsetfunktionen (z. B. OPENROWSET) an, die ein Objekt zurückgeben, das statt eines Tabellenverweises verwendet werden kann. Weitere Informationen zur Liste mit Rowsetfunktionen finden Sie unter Rowsetfunktionen (Transact-SQL).

    Ob ein Remoteobjekt mit der OPENROWSET-Funktion oder der OPENQUERY-Funktion angegeben wird, hängt von den Funktionen des OLE DB-Anbieters abhängt, der auf das Objekt zugreift. Weitere Informationen finden Sie unter Verteilte Abfragen.

  • bulk_column_alias
    Ein optionaler Alias, der einen Spaltennamen im Resultset ersetzen soll. Spaltenaliase sind nur in SELECT-Anweisungen zulässig, die die OPENROWSET-Funktion mit der BULK-Option verwenden. Wenn Sie bulk_column_alias verwenden, geben Sie einen Alias für jede Tabellenspalte in der gleichen Reihenfolge wie die Spalten in der Datei an.

    HinweisHinweis

    Dieser Alias setzt das NAME-Attribut in den COLUMN-Elementen einer XML-Formatdatei außer Kraft, sofern vorhanden.

  • user_defined_function
    Gibt eine Tabellenwertfunktion an.

  • OPENXML <openxml_clause>
    Stellt eine Rowsetsicht eines XML-Dokuments bereit. Weitere Informationen finden Sie unter OPENXML (Transact-SQL).

  • derived_table
    Eine Unterabfrage, die Zeilen aus der Datenbank abruft. Für die äußere Abfrage wird derived_table als Eingabe verwendet.

    derived_table kann mithilfe des Tabellenwertkonstruktors von Transact-SQL mehrere Zeilen angeben. Beispiel: SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);. Weitere Informationen finden Sie unter Tabellenwertkonstruktor (Transact-SQL).

  • column_alias
    Ein optionaler Alias, der einen Spaltennamen im Resultset der abgeleiteten Tabelle ersetzen soll. Geben Sie für jede Spalte in der Auswahlliste einen Spaltenalias an, und schließen Sie die gesamte Liste der Spaltenaliasnamen in Klammern ein.

  • <tablesample_clause>
    Gibt an, dass Beispieldaten aus der Tabelle zurückgegeben werden. Die Beispieldaten können ungefähr sein. Diese Klausel kann für eine primäre oder verknüpfte Tabelle in einer SELECT-, UPDATE- oder DELETE-Anweisung verwendet werden. TABLESAMPLE kann nicht für Sichten angegeben werden. Weitere Informationen finden Sie unter Beschränken von Resultsets mit TABLESAMPLE.

    HinweisHinweis

    Wenn Sie TABLESAMPLE für Datenbanken verwenden, die auf SQL Server aktualisiert werden, muss der Kompatibilitätsgrad der Datenbank auf mindestens 90 festgelegt werden. Informationen zum Festlegen des Kompatibilitätsgrads von Datenbanken finden Sie unter ALTER DATABASE-Kompatibilitätsgrad (Transact-SQL).

  • SYSTEM
    Eine von der Implementierung abhängige Stichprobenmethode, die durch ISO-Standards angegeben ist. In SQL Server ist dies die einzige verfügbare Stichprobenmethode. Sie wird standardmäßig angewendet. SYSTEM wendet eine seitenbasierte Stichprobenmethode an, in der eine zufällige Gruppe von Seiten aus der Tabelle für die Stichprobe ausgewählt wird. Alle Zeilen auf diesen Seiten werden als Stichprobenteilmenge zurückgegeben. Weitere Informationen finden Sie unter Beschränken von Resultsets mit TABLESAMPLE.

  • sample_number
    Ein genauer oder ungefährer konstanter numerischer Ausdruck, der den Prozentanteil oder die Anzahl der Zeilen angibt. Bei der Angabe für PERCENT wird sample_number implizit in einen float-Wert konvertiert. Anderenfalls wird das Argument in bigint konvertiert. PERCENT ist die Standardeinstellung.

  • PERCENT
    Gibt an, dass ein sample_number-Prozentanteil der Zeilen einer Tabelle aus der Tabelle abgerufen werden soll. Wenn PERCENT angegeben wird, gibt SQL Server einen ungefähren Wert des angegebenen Prozentanteils zurück. Wenn PERCENT angegeben wird, muss der sample_number-Ausdruck zu einem Wert von 0 bis 100 ausgewertet werden.

  • ROWS
    Gibt an, dass eine ungefähre sample_number-Zeilenanzahl abgerufen wird. Wenn ROWS angegeben ist, gibt SQL Server ungefähr die angegebene Zeilenanzahl zurück. Wenn ROWS angegeben ist, muss der sample_number-Ausdruck zu einem ganzzahligen Wert größer als null ausgewertet werden.

  • REPEATABLE
    Gibt an, dass die ausgewählten Stichprobendaten erneut zurückgegeben werden können. Bei Angabe mit demselben repeat_seed -Wert gibt SQL Server dieselbe Teilmenge von Zeilen zurück, wenn noch keine Änderungen an den Zeilen in der Tabelle vorgenommen wurden. Bei Angabe mit einem anderen repeat_seed-Wert gibt SQL Server möglicherweise einige andere Stichprobenzeilen in der Tabelle zurück. Die folgenden an der Tabelle vorgenommenen Aktionen gelten als Änderungen: Einfügen, Aktualisieren, Löschen, neues Erstellen oder Defragmentieren von Indizes sowie Wiederherstellen oder Anfügen von Datenbanken.

  • repeat_seed
    Eine konstante ganze Zahl, die von SQL Server zum Generieren einer zufälligen Zahl verwendet wird. repeat_seed ist ein Wert vom Datentyp bigint. Wenn repeat_seed nicht angegeben ist, weist SQL Server einen zufälligen Wert zu. Für einen bestimmten repeat_seed -Wert ist das Stichprobenergebnis immer gleich, wenn keine Änderungen auf die Tabelle angewendet wurden. Der repeat_seed-Ausdruck muss zu einem ganzzahligen Wert größer null ausgewertet werden.

  • <joined_table>
    Ein Resultset, das das Produkt von zwei oder mehr Tabellen darstellt. Verwenden Sie für mehrere Verknüpfungen Klammern, um die natürliche Verknüpfungsreihenfolge zu ändern.

  • <join_type>
    Gibt den Typ der Verknüpfungsoperation an.

  • INNER
    Gibt an, dass alle übereinstimmenden Paare von Zeilen zurückgegeben werden. Zeilen, die in den beiden Tabellen nicht übereinstimmen, werden verworfen. Wenn kein Verknüpfungstyp angegeben wird, ist dies die Standardeinstellung.

  • FULL [OUTER]
    Gibt an, dass eine Zeile aus der linken oder der rechten Tabelle im Resultset aufgeführt werden soll, die die Verknüpfungsbedingung nicht erfüllt. Die Ausgabespalten der anderen Tabelle werden in diesem Fall auf NULL festgelegt. Dies erfolgt zusätzlich zu allen Zeilen, die von INNER JOIN zurückgegeben werden.

  • LEFT [OUTER]
    Gibt an, dass alle Zeilen der linken Tabelle, die die angegebene Verknüpfungsbedingung nicht erfüllen, im Resultset enthalten sind. Die Ausgabespalten der anderen Tabelle werden auf NULL gesetzt. Dies erfolgt zusätzlich zu allen Zeilen, die von INNER JOIN zurückgegeben werden.

  • RIGHT [OUTER]
    Gibt an, dass alle Zeilen der rechten Tabelle, die die angegebene Verknüpfungsbedingung nicht erfüllen, im Resultset enthalten sind. Die Ausgabespalten der anderen Tabelle werden auf NULL gesetzt. Dies erfolgt zusätzlich zu allen Zeilen, die von INNER JOIN zurückgegeben werden.

  • <join_hint>
    Gibt an, dass der Abfrageoptimierer von SQL Server pro Verknüpfung genau einen in der FROM-Klausel angegebenen Verknüpfungshinweis oder Ausführungsalgorithmus verwendet. Weitere Informationen finden Sie unter Verknüpfungshinweise (Transact-SQL).

  • JOIN
    Legt fest, dass der angegebene Beitrittsvorgang mit den angegebenen Tabellenquellen oder Sichten durchgeführt werden soll.

  • ON <search_condition>
    Gibt die Bedingung an, auf der die Verknüpfung basiert. Als Bedingung können beliebige Prädikate angegeben werden, obwohl häufig Spalten- und Vergleichsoperatoren verwendet werden, z. B.:

    SELECT p.ProductID, v.VendorID
FROM Production.Product AS p 
JOIN Purchasing.ProductVendor AS v
ON (p.ProductID = v.ProductID);

    Wenn in der Bedingung Spalten angegeben werden, müssen die Spalten nicht denselben Namen oder Datentyp aufweisen. Die Datentypen müssen jedoch, wenn sie nicht identisch sind, kompatibel sein oder von SQL Server implizit konvertiert werden können. Wenn die Datentypen nicht implizit konvertiert werden können, muss die Bedingung den Datentyp mithilfe der CONVERT-Funktion explizit konvertieren.

    Es kann Prädikate geben, die sich auf nur eine der verknüpften Tabellen beziehen, die in der ON-Klausel angegeben sind. Derartige Prädikate können auch in der WHERE-Klausel der Abfrage stehen. Bei einer inneren Verknüpfung (INNER JOIN) spielt es keine Rolle, wo diese Prädikate stehen. Bei einer äußeren Verknüpfung (OUTER JOIN) können derartige Prädikate dagegen zu unterschiedlichen Ergebnissen führen. Dies liegt daran, dass die in der ON-Klausel angegebenen Prädikate vor der Verknüpfung auf die Tabelle angewendet werden, während die WHERE-Klausel semantisch auf das Ergebnis der Verknüpfung angewendet wird.

    Weitere Informationen zu Suchbedingungen und Prädikaten finden Sie unter Suchbedingung (Transact-SQL).

  • CROSS JOIN
    Gibt das Kreuzprodukt zweier Tabellen an. Gibt dieselben Zeilen wie die frühere Verknüpfungsanweisung (die nicht SQL-92-gemäß ist) ohne WHERE-Klausel zurück.

  • left_table_source{ CROSS | OUTER } APPLY right_table_source
    Gibt an, dass der right_table_source-Ausdruck des APPLY-Operators für jede Zeile des left_table_source-Ausdrucks ausgewertet wird. Diese Funktionalität ist hilfreich, wenn right_table_source eine Tabellenwertfunktion enthält, die Spaltenwerte aus left_table_source als eines ihrer Argumente verwendet.

    Mit APPLY muss CROSS oder OUTER angegeben werden. Wenn CROSS angegeben wird, werden keine Zeilen erstellt, wenn right_table_source für eine bestimmte Zeile von left_table_source ausgewertet wird. Das zurückgegebene Resultset ist leer.

    Wenn OUTER angegeben wird, wird selbst dann für jede Zeile von left_table_source eine Zeile erstellt, wenn right_table_source für diese Zeile ausgewertet wird. Das zurückgegebene Resultset ist leer.

    Weitere Informationen finden Sie im Abschnitt mit den Hinweisen und unter Verwenden von APPLY.

  • left_table_source
    Eine Tabellenquelle gemäß Definition im vorherigen Argument. Weitere Informationen finden Sie im Abschnitt mit den Hinweisen.

  • right_table_source
    Eine Tabellenquelle gemäß Definition im vorherigen Argument. Weitere Informationen finden Sie im Abschnitt mit den Hinweisen.

  • table_source PIVOT <pivot_clause>
    Gibt an, dass table_source auf der Basis von pivot_column pivotiert wird. table_source ist eine Tabelle oder ein Tabellenausdruck. Die Ausgabe ist eine Tabelle, die alle Spalten von table_source mit Ausnahme von pivot_column und value_column enthält. Die Spalten von table_source mit Ausnahme von pivot_column und value_column werden als Gruppierungsspalten des PIVOT-Operators bezeichnet.

    PIVOT führt einen Gruppierungsvorgang für die Eingabetabelle in Bezug auf die Gruppierungsspalten aus und gibt für jede Gruppe eine Zeile zurück. Zusätzlich enthält die Ausgabe eine Spalte für jeden Wert, der in column_list angegeben ist. Diese Liste wird in der pivot_column-Spalte der input_table-Tabelle angezeigt.

    Weitere Informationen finden Sie im Abschnitt mit den Hinweisen und unter Verwenden von PIVOT und UNPIVOT.

    HinweisHinweis

    Wenn Sie PIVOT für Datenbanken verwenden, die auf SQL Server aktualisiert werden, muss der Kompatibilitätsgrad der Datenbank auf mindestens 90 festgelegt sein. Weitere Informationen zum Festlegen des Kompatibilitätsgrads der Datenbank finden Sie unter ALTER DATABASE-Kompatibilitätsgrad (Transact-SQL).

  • aggregate_function
    Eine system- oder benutzerdefinierte Aggregatfunktion, die mehrere Eingaben akzeptiert. Die Aggregatfunktion muss bezüglich NULL-Werten invariant sein. Eine bezüglich NULL-Werten invariante Aggregatfunktion berücksichtigt beim Auswerten des Aggregatwerts keine NULL-Werte in der Gruppe.

    Die systembasierte Aggregatfunktion COUNT(*) ist nicht zulässig.

  • value_column
    Die Wertspalte des PIVOT-Operators. Bei Verwendung mit UNPIVOT kann value_column nicht der Name einer vorhandenen Spalte in der table_source-Eingabe sein.

  • FOR pivot_column
    Die Pivotspalte des PIVOT-Operators. Der Typ der pivot_column-Spalte muss implizit oder explizit in nvarchar() konvertierbar sein. Diese Spalte kann nicht image oder rowversion sein.

    Wenn UNPIVOT verwendet wird, ist pivot_column der Name der Ausgabespalte, zu der table_source eingeschränkt wird. In table_source kann es keine vorhandene Spalte mit diesem Namen geben.

  • IN ( column_list )
    Führt in der PIVOT-Klausel die Werte in der pivot_column-Spalte auf, die zu den Spaltennamen der Ausgabetabelle werden. In der Liste können keine Spaltennamen angegeben werden, die bereits in der zu pivotierenden table_source-Eingabe vorhanden sind.

    Führt in der UNPIVOT-Klausel die Spalten in table_source auf, die zu einer einzelnen pivot_column-Spalte eingeschränkt werden.

  • table_alias
    Der Aliasname der Ausgabetabelle. pivot_table_alias muss angegeben werden.

  • UNPIVOT < unpivot_clause >
    Gibt an, dass die Eingabetabelle aus mehreren Spalten in column_list zu einer einzelnen Spalte namens pivot_column eingeschränkt wird.

    Weitere Informationen finden Sie im Abschnitt mit den Hinweisen und unter Verwenden von PIVOT und UNPIVOT.

    HinweisHinweis

    Wenn Sie UNPIVOT für Datenbanken verwenden, die auf SQL Server aktualisiert werden, muss der Kompatibilitätsgrad der Datenbank auf mindestens 90 festgelegt sein. Informationen zum Festlegen des Kompatibilitätsgrads von Datenbanken finden Sie unter ALTER DATABASE-Kompatibilitätsgrad (Transact-SQL).

Hinweise

Die FROM-Klausel unterstützt die SQL-Syntax von SQL-92 für verknüpfte und abgeleitete Tabellen. Die SQL-92-Syntax stellt die Verknüpfungsoperatoren INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER und CROSS zur Verfügung.

UNION und JOIN in einer FROM-Klausel werden in Sichten, abgeleiteten Tabellen und Unterabfragen unterstützt.

Eine Selbstverknüpfung ist eine Tabelle, die mit sich selbst verknüpft ist. Vorgänge zum Einfügen und Aktualisieren, die auf einer Selbstverknüpfung basieren, werden gemäß der Reihenfolge in der FROM-Klausel ausgeführt.

Frühere Versionen von SQL Server unterstützen die Definition von äußeren Verknüpfungen, die die Operatoren *= und =* in der WHERE-Klausel verwenden. SQL Server Version&nbsp;7.0 unterstützt den ISO-Standard. Damit werden Verknüpfungsoperatoren in der FROM-Klausel zur Verfügung gestellt. Wenn als Kompatibilitätsgrad der Datenbank 90 oder ein höherer Wert festgelegt ist, werden die äußeren Verknüpfungsoperatoren (*= und =*) nicht unterstützt.

Da SQL Server Verteilungs- und Kardinalitätsstatistiken von Verbindungsservern berücksichtigt, die Spaltenverteilungsstatistiken bereitstellen, ist der REMOTE-Verknüpfungshinweis nicht erforderlich, um eine Remotebewertung einer Verknüpfung zu erzwingen. Der SQL Server-Abfrageprozessor berücksichtigt Remotestatistiken und bestimmt, ob eine Remoteverknüpfungsstrategie geeignet ist. Der REMOTE-Verknüpfungshinweis ist für Anbieter nützlich, die keine Spaltenverteilungsstatistiken bereitstellen. Weitere Informationen finden Sie unter Anforderungen an die Verteilungsstatistik für OLE DB-Anbieter.

Weitere Informationen zum Arbeiten mit Verknüpfungen finden Sie unter Grundlegendes zu Verknüpfungen und Verwenden von Verknüpfungen.

Verwenden von APPLY

Der linke und der rechte Operand des APPLY-Operators sind Tabellenausdrücke. Der Hauptunterschied zwischen diesen Operanden besteht darin, dass right_table_source eine Tabellenwertfunktion verwenden kann, die eine Spalte aus left_table_source als eines der Argumente der Funktion verwendet. left_table_source kann Tabellenwertfunktionen einschließen. Dieser Operand kann allerdings keine Argumente enthalten, die Spalten aus right_table_source sind.

Der APPLY-Operator funktioniert folgendermaßen, um die Tabellenquelle für die FROM-Klausel zu erstellen:

  1. Wertet right_table_source für jede Zeile von left_table_source aus, um Rowsets zu erstellen.

    Die Werte in right_table_source hängen von left_table_source ab. right_table_source lässt sich ungefähr folgendermaßen darstellen: TVF(left_table_source.row), wobei TVF eine Tabellenwertfunktion ist.

  2. Kombiniert die Resultsets, die für die einzelnen Zeilen in der Auswertung von right_table_source mit left_table_source unter Ausführung eines UNION ALL-Vorgangs erstellt werden.

    Die Liste der Spalten, die durch das Ergebnis des APPLY-Operators erstellt wurde, ist die Gruppe der Spalten aus left_table_source, die mit der Liste der Spalten aus right_table_source kombiniert wird.

Verwenden von PIVOT und UNPIVOT

Bei pivot_column und value_column handelt es sich um Gruppierungsspalten, die vom PIVOT-Operator verwendet werden. PIVOT führt dabei die folgenden Schritte aus, um das Ausgaberesultset zu erhalten:

  1. Führt einen GROUP BY-Vorgang für input_table für die Gruppierungsspalten aus und erstellt eine Ausgabespalte für jede Gruppe.

    Die Gruppierungsspalten in der Ausgabespalte erhalten für diese Gruppe in input_table die entsprechenden Spaltenwerte.

  2. Generiert folgendermaßen Werte für die Spalten in der Spaltenliste für jede Ausgabezeile:

    1. Zusätzliches Gruppieren der Zeilen für pivot_column, die im vorherigen Schritt in GROUP BY generiert wurden.

      Auswählen einer Untergruppe für jede Ausgabespalte in column_list. Die Untergruppe erfüllt folgende Bedingung:

      pivot_column = CONVERT(<data type of pivot_column>, 'output_column')

    2. aggregate_function wird für value_column bei dieser Untergruppe ausgewertet. Das Ergebnis wird als der Wert der entsprechenden output_column-Spalte ausgegeben. Wenn die Untergruppe leer ist, generiert SQL Server einen NULL-Wert für diese output_column-Spalte. Sind die Aggregatfunktion COUNT und die Untergruppe leer, wird NULL (0) zurückgegeben.

Weitere Informationen finden Sie unter Verwenden von PIVOT und UNPIVOT.

Berechtigungen

Erfordert die Berechtigungen für die DELETE-, SELECT- oder UPDATE-Anweisung.

Beispiele

A. Verwenden einer einfachen FROM-Klausel

Das folgende Beispiel ruft die TerritoryID- und die Name-Spalten aus der SalesTerritory-Tabelle in der AdventureWorks-Stichprobendatenbank ab.

USE AdventureWorks ;
GO
SELECT TerritoryID, Name
FROM Sales.SalesTerritory
ORDER BY TerritoryID ;

Dies ist das Resultset.

TerritoryID Name                          
----------- ------------------------------
1           Northwest                     
2           Northeast                     
3           Central                       
4           Southwest                     
5           Southeast                     
6           Canada                        
7           France                        
8           Germany                       
9           Australia                     
10          United Kingdom                
(10 row(s) affected)

B. Verwenden der Hinweise TABLOCK und HOLDLOCK für den Abfrageoptimierer

Die folgende Teiltransaktion zeigt, wie eine explizite freigegebene Tabellensperre auf die Employee-Tabelle angewendet und der Index gelesen wird. Die Sperre bleibt während der gesamten Transaktion bestehen.

USE AdventureWorks ;
GO
BEGIN TRAN
SELECT COUNT(*) 
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;

C. Verwenden der CROSS JOIN-Syntax gemäß SQL-92

Im folgenden Beispiel wird das Kreuzprodukt der beiden Tabellen Employee und Department zurückgegeben. Eine Liste aller möglichen Kombinationen der EmployeeID-Spalten und aller Department -Namenspalten wird zurückgegeben.

USE AdventureWorks ;
GO
SELECT e.EmployeeID, d.Name AS Department
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d
ORDER BY e.EmployeeID, d.Name ;

D. Verwenden der FULL OUTER JOIN-Syntax gemäß SQL-92

Im folgenden Beispiel werden der Produktname und alle zugehörigen Kaufaufträge in der SalesOrderDetail-Tabelle zurückgegeben. Außerdem werden Kaufaufträge zurückgegeben, für die kein Produkt in der Product-Tabelle aufgeführt ist. Darüber hinaus werden Produkte mit einem anderen Kaufauftrag als dem in der Product-Tabelle aufgeführten Kaufauftrag zurückgegeben.

USE AdventureWorks ;
GO
-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
FULL OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
WHERE p.ProductID IS NULL OR sod.ProductID IS NULL
ORDER BY p.Name ;

E. Verwenden der LEFT OUTER JOIN-Syntax gemäß SQL-92

Im folgenden Beispiel werden zwei Tabellen über ProductID verknüpft und die nicht entsprechenden Zeilen aus der linken Tabelle aufbewahrt. Die Product-Tabelle wird mit der SalesOrderDetail-Tabelle über die ProductID-Spalten in den beiden Tabellen verglichen. Im Resultset werden alle bestellten und nicht bestellten Produkte angezeigt.

USE AdventureWorks ;
GO
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;

F. Verwenden der INNER JOIN-Syntax gemäß SQL-92

Im folgenden Beispiel werden alle Produktnamen und Verkaufsauftragsnummern zurückgegeben.

USE AdventureWorks ;
GO
-- By default, SQL Server performs an INNER JOIN if only the JOIN 
-- keyword is specified.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;

G. Verwenden der RIGHT OUTER JOIN-Syntax gemäß SQL-92

Im folgenden Beispiel werden zwei Tabellen über TerritoryID verknüpft und die nicht entsprechenden Zeilen aus der rechten Tabelle aufbewahrt. Die SalesTerritory-Tabelle wird mit der SalesPerson-Tabelle über die TerritoryID-Spalte in den beiden Tabellen verglichen. Im Resultset werden alle Vertriebsmitarbeiter unabhängig davon aufgeführt, ob ihnen ein Gebiet zugewiesen ist.

USE AdventureWorks ;
GO
SELECT st.Name AS Territory, sp.SalesPersonID
FROM Sales.SalesTerritory AS st 
RIGHT OUTER JOIN Sales.SalesPerson AS sp
ON st.TerritoryID = sp.TerritoryID ;

H. Verwenden der Verknüpfungshinweise HASH und MERGE

Im folgenden Beispiel werden die drei Tabellen Product, ProductVendor und Vendor verknüpft, um eine Liste der Produkte und deren Anbieter zu produzieren. Der Abfrageoptimierer verknüpft Product und ProductVendor (p und pv) mit einer MERGE-Verknüpfung. Das Ergebnis der MERGE-Verknüpfung von Product und ProductVendor (p und pv) wird mit der Vendor-Tabelle verknüpft und ergibt (p und pv) und v. Dabei wird eine HASH-Verknüpfung angewendet.

Wichtiger HinweisWichtig

Nach Angabe eines Verknüpfungshinweises muss das INNER-Schlüsselwort für jede auszuführende innere Verknüpfung explizit angegeben werden.

 
USE AdventureWorks ;
GO
SELECT p.Name AS ProductName, v.Name AS VendorName
FROM Production.Product AS p 
INNER MERGE JOIN Purchasing.ProductVendor AS pv 
ON p.ProductID = pv.ProductID
INNER HASH JOIN Purchasing.Vendor AS v
ON pv.VendorID = v.VendorID
ORDER BY p.Name, v.Name ;

I. Verwenden einer abgeleiteten Tabelle

Im folgenden Beispiel wird eine abgeleitete Tabelle verwendet. Mit einer SELECT-Anweisung nach der FROM-Klausel werden die Vor- und Nachnamen aller Mitarbeiter und ihre Wohnorte zurückgegeben.

USE AdventureWorks ;
GO
SELECT RTRIM(c.FirstName) + ' ' + LTRIM(c.LastName) AS Name,
 d.City
FROM Person.Contact AS c
INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID 
INNER JOIN
   (SELECT ea.AddressID, ea.EmployeeID, a.City 
    FROM Person.Address AS a
    INNER JOIN HumanResources.EmployeeAddress AS ea
    ON a.AddressID = ea.AddressID) AS d
ON e.EmployeeID = d.EmployeeID
ORDER BY c.LastName, c.FirstName;

J. Verwenden von TABLESAMPLE zum Lesen von Daten aus Stichprobenzeilen in einer Tabelle

Im folgenden Beispiel wird TABLESAMPLE in der FROM-Klausel verwendet, um ungefähr 10 Prozent aller Zeilen in der Customer-Tabelle in der AdventureWorks-Datenbank zurückzugeben.

USE AdventureWorks ;
GO
SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;

K. Verwenden von APPLY

Im folgenden Beispiel wird das Vorhandensein der folgenden Tabellen mit dem folgenden Schema in der Datenbank vorausgesetzt:

  • Departments: DeptID, DivisionID, DeptName, DeptMgrID

  • EmpMgr: MgrID, EmpID

  • Employees: EmpID, EmpLastName, EmpFirstName, EmpSalary

Außerdem ist eine Tabellenwertfunktion vorhanden: GetReports(MgrID) gibt eine Liste der MgrID direkt oder indirekt unterstellten Mitarbeiter zurück (EmpID, EmpLastName, EmpSalary).

Im Beispiel werden mit APPLY alle Abteilungen und alle Mitarbeiter in dieser Abteilung zurückgegeben. Wenn eine bestimmte Abteilung keine Mitarbeiter hat, werden für diese Abteilung keine Zeilen zurückgegeben.

SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;

Wenn die Abfrage Zeilen für diese Abteilungen ohne Mitarbeiter produzieren soll, die NULL-Werte für die Spalten EmpID, EmpLastName und EmpSalary produziert, verwenden Sie stattdessen OUTER APPLY.

SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d OUTER APPLY dbo.GetReports(d.DeptMgrID) ;

L. Verwenden von PIVOT und UNPIVOT

Im folgenden Beispiel wird die Anzahl der Bestellungen zurückgegeben, die von den Mitarbeiter-IDs 164, 198, 223, 231 und 233 nach Anbieter-ID sortiert aufgegeben wurden.

USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) AS p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;

Im Folgenden wird ein Teil des Resultsets aufgeführt:

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5

----------------------------------------------------------------

1           4           3           5           4           4

2           4           1           5           5           5

3           4           3           5           4           4

4           4           2           5           5           4

5           5           1           5           5           5

Um UNPIVOT für die Tabelle auszuführen, wird davon ausgegangen, dass das im vorherigen Beispiel produzierte Resultset als pvt gespeichert wurde. Die Abfrage sieht folgendermaßen aus.

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE dbo.pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
-- Insert 5 rows into the table.
INSERT INTO dbo.pvt VALUES (1,4,3,5,4,4),(2,4,1,5,5,5),(3,4,3,5,4,4),
                           (4,4,2,5,5,4),(5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
    (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM dbo.pvt) AS p
UNPIVOT
    (Orders FOR Employee IN 
        (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Im Folgenden wird ein Teil des Resultsets aufgeführt:

VendorID    Employee    Orders

------------------------------

1           Emp1        4

1           Emp2        3

1           Emp3        5

1           Emp4        4

1           Emp5        4

2           Emp1        4

2           Emp2        1

2           Emp3        5

2           Emp4        5

2           Emp5        5