3
votes

Sortie de colonne dynamique de requête de sélection SQL Server

Je souhaite accomplir ce qui suit dans SQL Server 2008

J'ai un tableau d'articles comme suit

| ArticleId | Description | Week51 | Week52 | Week1 | Week2 | Week3 |
|-----------+-------------+-----------------+-------+-------+-------+
|         1 | Test        |     0  |    150 |     0 |   200 |     0 |
|-----------+-------------+--------+--------+-------+-------+-------+

Et un tableau de prévisions de commandes comme celui-ci.

| ArticleId | Week | Order | Amount |
|-----------+--------------+--------+
|         1 |   51 |     1 |      0 |
|         1 |   52 |     2 |    150 |
|         1 |    1 |     3 |      0 |
|         1 |    2 |     4 |    200 |
|         1 |    3 |     5 |      0 |
|-----------+------+-------+--------+

Existe-t-il un moyen de créer une requête qui produit une colonne pour chaque enregistrement de la table de prévision dans l'ordre de la colonne order Si c'est possible, comment pourrais-je faire ça?

| ArticleId | Description |
|-----------+-------------|
|         1 | Test        |
|-----------+-------------|

sql sql-server sql-server-2008

3 commentaires

Sans utiliser SQL dynamique, vous êtes lié à une instruction CASE avec X semaines d'arguments ou à un PIVOT avec X colonnes pour les numéros de semaine, cependant, je ne sais pas comment masquer les colonnes nulles.

Vous avez besoin d'un pivot SQL dynamique qui crée la liste de sélection dans l'ordre spécifié. stackoverflow.com/questions/19015950/…

Copie possible de la requête PIVOT dynamique SQL Server? et / ou Pivot dynamique T-SQL ?

3 Réponses :

1
votes

Dans le cas général, le langage SQL exige strictement que le nombre et les types de données des colonnes de résultats soient connus au moment de la compilation de la requête. Ce que vous demandez ici ne peut être connu qu'après la préparation du plan d'exécution et la recherche des données.

Par conséquent, le mieux que vous puissiez faire en général est d'exécuter ceci en trois étapes distinctes:

Exécutez une requête pour vous indiquer les colonnes que vous souhaitez utiliser.
Utilisez les résultats de # 1 pour rédiger une nouvelle requête à la volée.
Exécutez la nouvelle requête et renvoyez les résultats

Même dans ce cas, ce type de pivot est généralement mieux géré dans votre code client ou votre outil de rapport. La seule bonne nouvelle est qu'il est toujours possible d'accomplir tout cela à partir de la plupart des plates-formes avec une seule longue chaîne SQL.

Pour cette situation spécifique, où vous regardez clairement les numéros de semaine, vous pouvez contourner le problème en supposant toutes les 53 semaines possibles à l'avance (pas 52, en raison de semaines partielles à la fin de l'année!), et écrire une grande instruction SQL qui comptabilise manuellement les 55 colonnes (53 semaines + article et description).

1 commentaires

Ouais, j'avais peur que ce soit la réponse, l'outil dont j'ai besoin n'est pas vraiment convivial pour les scripts. Voilà pourquoi ma question.

0
votes

Vous pouvez essayer la requête suivante en utilisant Pivot pour le résultat souhaité.

ArticleId   Description Week51  Week52  Week1   Week2   Week3
-------------------------------------------------------------
1           Test        0       150     0       200     0

Le résultat sera comme indiqué ci-dessous

Create Table Article (ArticleId Int, [Description] Varchar(10))
Insert Into Article Values (1, 'Test')

Create Table OrderForecast(ArticleId Int, [Week] Int, [Order] Int, Amount Int)
Insert Into OrderForecast Values (1, 51, 1, 0),(1, 52, 2, 150), (1, 1, 3, 0),(1, 2, 4, 200), (1, 3, 5,0)

Select ArticleId, [Description], Week51, Week52, Week1, Week2, Week3
from
(
  select ArticleId, [Description], Amount, [Week]
  from 
    (
    SELECT OrderForecast.ArticleId, 'Week' + Convert(Varchar(10), OrderForecast.[Week]) as [Week], [Order], Amount, 
        Article.[Description] as [Description] FROM OrderForecast
    Inner Join Article On OrderForecast.ArticleId = Article.ArticleId
    )a
) d
pivot
(
  max(Amount)
  for [Week] in (Week51, Week52, Week1, Week2, Week3)
) piv;

Ici J'ai utilisé la requête comme table parce que la semaine était en nombres comme 1, 2 mais vous voulez le résultat dans la semaine1, la semaine2, etc. J'ai donc concaténé le mot Semaine dans le nombre et l'ai utilisé dans la requête Pivot.

Vous pouvez trouver la démo en direct Démo en direct ici

0 commentaires

2
votes

À condition que les numéros de SEMAINE et les numéros de commande soient cohérents, il suffit de maintenir la séquence des colonnes.

Vous remarquerez peut-être que j'ai utilisé #forecast et #article car je ne connaissais pas vos noms de table. >

Exemple

ArticleID   Description Week51  Week52  Week1   Week2   Week3
1           Test        0       150     0       200     0

Retours

Declare @SQL varchar(max) = '
Select *
 From (
        Select A.ArticleID
              ,D.Description 
              ,B.*
         From  #forecast  A
         Join  #article   D on A.ArticleID=D.ArticleID
         Cross Apply (values (''Week''+left(Week,4),Amount) ) B(Item,Value)
      ) A
 Pivot (max([Value]) 
        For [Item] in (' + Stuff((Select ','+QuoteName('Week'+left(Week,4)) 
                                   From (Select Distinct top 100 [Order],Week From #forecast Order by [Order]  ) A  
                                   For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL

p >

0 commentaires