Comment-Pour développer requête croisée dans T-SQL (Transact-SQL) avec des colonnes dynamiques


Création Croix Tab Style requête dans SQL Server est toujours un défi. Plusieurs modèles de conception émergent au cours des années que je ai toujours trouvé que ce était un peu défaut. Ce tutoriel va montrer un simple mais très puissant pour créer Croix Tab requête avec des colonnes dynamiques. Je entends par là que vous ne connaissez pas les noms des colonnes à l'avance.

Ma méthode consiste à utiliser «XML» qui a également été introduit avec SQL Server 2000 pour fournir un moyen de faire la concaténation de chaînes. Cela a été encore élargi avec SQL Server 2005 pour inclure la clause de chemin que nous allons utiliser pour générer nos colonnes dynamiques. Le reste de cet article va montrer où à utiliser pour XML Chemin de Pivot qui a été présenté SQL Server 2005 pour créer une croix flexible avec des colonnes dynamiques.

je vais faire ce qui suit:

  1. Créez des variables
  2. Sélectionnez les valeurs pour mes colonnes que xml dynamique
  3. Xml convertir en une chaîne
  4. Écrire la requête
  5. Exécutez la requête





Créez des variables

Nous allons avoir besoin de trois variables pour cet exemple: une pour le XML, un pour la requête SQL et un pour la colonne de chaîne. SQL est la requête SQL; XML est le XML qui sera créée avec pour XML Path et infinecols pour la colonne de chaîne à utiliser dans la procédure pivot:


DichiarareSQL VARCHAR (8000),
XML XML,
cols VARCHAR (4000)


Ensuite, nous allons obtenir notre liste de colonnes sur la base des valeurs d'une requête qui renvoie une liste de valeurs qui peuvent être utilisés comme noms de colonnes. Dans la requête suivante, nous sélectionnons une liste de noms de produits de la table ProductsCatalog. La seule réserve est que les valeurs que nous sélectionnons doivent également être ici dans le tableau que nous allons utiliser pour notre croix de requête.

Il y aura ensuite être enfermés entre crochets parce que la fonction de pivot dans SQL Server en tant que partie de la syntaxe exige. Ce est une simple SELECT je cède mes variabilecols. Cette requête peut être aussi complexe que vous devez être à retourner une seule colonne de valeurs pour les en-têtes de colonnes dans le jeu de résultats Cross Tab.



ImpostaCOL = (SELECT ', [' + Produits + ']'
DA ProductsCatalog
GROUP BY SKU
ORDER BY SKU
POUR XML PATH ('')
)


La sortie XML serait comme ça:


, [Widget1]
[Widget2]
[Widget3]
[Widget4]
[Widget5]
[Widget6]


Cependant, vous devriez vraiment voir des résultats dans SSMS suivantes hyperlien de la façon suivante:

, [Widget1], [Widget2], [Widget3], [Widget4], [Widget5], [Widget6], [Widget7]

Convertir XML dans une chaîne

Conversion de sortie XML dans une chaîne est assez simple. Il vous suffit de convertir votre XML VARCHAR et indiquer que vous souhaitez valeurs distinctes. Vous pouvez affecter cette chaîne à une variable, comme vous pouvez le voir ci-dessous.

cols = SET (SELECT DISTINCT CONVERT (VARCHAR (4000), cols))

La deuxième série est de supprimer le leader de virgule "," par la chaîne.

SETCOLS = substring (COLS, 2, LEN (cols))

Maintenant, les deux seules étapes qui restent sont à écrire des requêtes dont la clause PIVOT et exécuter la requête. En premier lieu la question, ici, je me sers d'une clause Select pour récupérer les colonnes pour mon Pivot. Les données proviennent de la sous-requête et est utilisé pour ajouter les valeurs dans la colonne à l'aide val POUR [col3] clause. Remarque colonne col3 est entre crochets [] et est la même colonne qui a été utilisé dans la sélection des noms de colonnes dans la requête ci-dessus. Enfin, les noms de colonnes sont enfermées dans une autre série de crochets et PIVOT doivent être affectés un alias "TP". La même chose se applique à la nomenclature données de requête; doit avoir un alias "données". Enfin, la requête est exécutée avec un Exec simple (SQL).

SETSQL = 'Select * from
(
Sélection des produits, salesValues
par ProductSales
) Données
PIVOT
(
Somme (salesValues) POUR [produits] IN ('+ @ + Cols »)
) Pt '
Exec (SQL)

Ce est un exemple très simple d'expliquer les concepts dans un espérons clair. Ce modèle présente un colonnes simples et hautement dynamiques pour générer croisé dynamique pour vos questions.

(0)
(0)

Commentaires - 0

Sans commentaires

Ajouter un commentaire

smile smile smile smile smile smile smile smile
smile smile smile smile smile smile smile smile
smile smile smile smile smile smile smile smile
smile smile smile smile
Caractères restants: 3000
captcha