MS SQL Server
  1. MS SQL Server
  3. Colonnes dynamics – SQL Server – Mois en tant que colonnes
Intereting Posts
Comment puis-je mettre à jour une valeur dans une table en utilisant une clause de jointure dans la mise à jour? Transaction partagée sur plusieurs connections ou ReadUncommitted dans PostgreSQL Sélectionnez une string jusqu'au premier ou deuxième espace dans la string Pourquoi une jointure SQL choisit-elle un plan de requête sous-optimal? Comment implémenter une relation plusieurs à plusieurs dans ASP.NET à l'aide de SQL Server Problèmes lors de l'insertion de string dans SQL Server Requête SQL pour remplir les espaces manquants dans le time et get la dernière valeur non nulle fonction ne fonctionne pas dans CHECK Contrainte sur le server SQL Copier des données d'une table SQL Server dans une table historique et append un horodatage de l'heure copiée? Le server sql mange de la memory mais ne le laisse pas libre une fois que c'est fait SQL Server While Loop ne renvoyant pas la valeur attendue Somme des instances de valeurs différentes et total leur prix Solution pour faire des migrations de database sur Oracle / SqlServer et SqlLite SQL Server OpenQuery – Transmission du résultat du paramètre de la requête SELECT principale à la sous-requête comment puis-je mettre à jour les 100 meilleurs loggings dans le server SQL

Colonnes dynamics – SQL Server – Mois en tant que colonnes

DB: SQL Server 2005

Nous avons une table qui a des données de cette manière: 

Project Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec -------------------- ----------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- 11-11079 2008 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 75244.90 11-11079 2009 466.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 11-11079 2010 855.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 01-11052 2009 56131.00 0.00 36962.00 -61596.00 2428.00 84.00 0.00 0.00 0.00 0.00 0.00 0.00

Quelqu'un voudrait que datatables soient affichées en une seule ligne pour l'set du projet. Les colonnes seraient dynamics en fonction du nombre d'années passées dans le futur. Un exemple serait: 

 Project Jan-2009 Feb-2009 Mar-2009 Apr-2009... Dec-2009 Jan-2010 -------------- ------------ ------------ ------------ ----------- ------------ --------- 11-11079 466.00 0.00 0.00 0.00 0.00 855.00 01-11052 56131.00 0.00 36962.00 -61596.00 2428.00 0.00

J'ai lu de nombreux exemples où la date est remplie dans une colonne pour chaque input, mais je n'ai trouvé aucun cas où les mois sont le nom de la colonne et l'année est dans la ligne.

SQL dynamic avec un tableau croisé dynamic?
Ou une manipulation à grande échelle en utilisant SQL, les tables temporaires, les jointures et les unions?
Des reflections sur l'utilisation de la fonctionnalité de table pivotante SSIS?

Vos données sont déjà pivotées, mais doivent être pivotées à un niveau différent. Je pense que la meilleure façon de gérer cela est de le déplier d'abord, puis de gérer le niveau de pivot correct en second.

Étape 1: Unpivot

Vous pouvez utiliser la command SQL 2005 UNPIVOT ou utiliser une technique CROSS JOIN. Voici des exemples des deux. Remarque J'ai laissé de côté des mois au milieu pour garder les choses simples. Ajoutez-les simplement. 

 -- CROSS JOIN method (also works in SQL 2000) SELECT P.Project, Mo = DateAdd(mm, X.MonthNum, DateAdd(yy, P.[Year] - 1900, '19000101') ), Amount = CASE X.MonthNum WHEN 0 THEN Jan WHEN 1 THEN Feb WHEN 11 THEN Dec END FROM ProjectData P CROSS JOIN ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 11 ) X (MonthNum)

Chaque ligne est répétée 12 fois, puis une instruction CASE ne sort que d'un mois pour chaque ligne, laissant datatables bien non pivotées. 

 -- UNPIVOT method SELECT P.Project, Mo = DateAdd(mm, Convert(int, P.MonthNum), DateAdd(yy, P.[Year] - 1900, '19000101') ), P.Amount FROM ( SELECT Project, [Year], [0] = Jan, [1] = Feb, [11] = Dec FROM ProjectData ) X UNPIVOT (Amount FOR MonthNum IN ([0], [1], [11])) P DROP TABLE ProjectData

Aucune des deux methods n'est un gagnant de performance clair tout le time. Parfois, l'un fonctionne mieux que l'autre (en fonction des données pivotées). La méthode UNPIVOT utilise un filter dans le plan d'exécution que le CROSS JOIN ne fait pas.

Étape 2: Pivoter à nouveau

Maintenant, comment utiliser datatables non pivotées. Vous n'avez pas dit comment votre personne va consumr cela, mais puisque vous aurez besoin de mettre datatables dans un file de sortie quelconque, je propose d'utiliser SSRS (Sql Server Reporting Services), qui vient avec SQL Server 2005 pour non supplément.

Utilisez simplement l'object de rapport Masortingx pour faire pivoter l'une des requêtes ci-dessus. Cet object détermine avec bonheur les valeurs de données à convertir en labels de colonne lors de l'exécution du rapport et sonne exactement comme vous le souhaitez. Si vous ajoutez une colonne qui formate la date exactement comme vous le souhaitez, vous pouvez l'ordonner par la colonne Mo, mais utiliser la nouvelle expression comme libellé de colonne.

SSRS propose également une grande variété de formats et d'options de planification. Par exemple, vous pouvez l'envoyer par courrier électronique à un file Excel ou save une page Web dans un partage de files.

S'il vous plaît laissez-moi savoir si j'ai laissé quelque chose.

Pour tous ceux qui aimeraient voir le code ci-dessus en action, voici un script de création pour vous: 

 USE tempdb CREATE TABLE ProjectData ( Project varchar(10), [Year] int, Jan decimal(15, 2), Feb decimal(15, 2), Dec decimal(15, 2) ) SET NOCOUNT ON INSERT ProjectData VALUES ('11-11079', 2008, 0.0, 0.0, 75244.90) INSERT ProjectData VALUES ('11-11079', 2009, 466.0, 0.0, 0.0) INSERT ProjectData VALUES ('11-11079', 2010, 855.0, 0.0, 0.0) INSERT ProjectData VALUES ('01-11052', 2009, 56131.0, 0.0, 0.0)

J'ai écrit un proc stocké nommé pivot_query qui peut aider avec ceci, la source est ici , des exemples avec des données brutes ici . 

 With your data: create table ProjectData ( Project varchar(20), [Year] Integer, Jan decimal(12,2), Feb decimal(12,2), Mar decimal(12,2), Apr decimal(12,2), May decimal(12,2), Jun decimal(12,2), Jul decimal(12,2), Aug decimal(12,2), Sep decimal(12,2), Oct decimal(12,2), Nov decimal(12,2), Dec decimal(12,2) ); insert into ProjectData values ('11-11079',2008, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 75244.90); insert into ProjectData values ('11-11079',2009, 466.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00); insert into ProjectData values ('11-11079',2010, 855.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00) ; insert into ProjectData values ('01-11052',2009, 56131.00, 0.00, 36962.00, -61596.00, 2428.00, 84.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00); declare @mySQL varchar(MAX) set @mySQL = 'select * from ProjectData' exec pivot_query @mySQL, 'Project', 'Year', 'max(Jan) Jan,max(Feb) Feb,max(Mar) Mar,max(Apr) Apr,max(Jun) Jun,max(Jul) Jul,max(Aug) Aug,max(Sep) Sep,max(Oct) Oct,max(Nov) Nov,max(Dec) Dec' Results: Project 2008_Jan 2008_Feb 2008_Mar 2008_Apr 2008_Jun 2008_Jul 2008_Aug 2008_Sep 2008_Oct 2008_Nov 2008_Dec 2009_Jan 2009_Feb 2009_Mar 2009_Apr 2009_Jun 2009_Jul 2009_Aug 2009_Sep 2009_Oct 2009_Nov 2009_Dec 2010_Jan 2010_Feb 2010_Mar 2010_Apr 2010_Jun 2010_Jul 2010_Aug 2010_Sep 2010_Oct 2010_Nov 2010_Dec -------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ 01-11052 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 56131.00 .00 36962.00 -61596.00 84.00 .00 .00 .00 .00 .00 .00 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 11-11079 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 75244.90 466.00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 855.00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00

Pas exact mais assez proche. 🙂

Je pense que vous pourriez le faire avec une boucle while nestede et du SQL dynamic. Ce serait une solution lente si vous ne pouvez pas save la table finale ou si vous devez régénérer toutes les colonnes tous les mois. Cependant, si c'est juste additif, cela pourrait ne pas être mauvais. De toute façon, voici comment je le ferais:

  1. La boucle de sortie choisit l'année la plus ancienne.
  2. Boucle interne choisit le premier mois.
  3. Inside Inner loop – Ajouter une colonne avec le nom de – à votre table.
  4. Inside Inner loop – Mettre à jour la table avec toutes les informations pour la nouvelle colonne avec SQL dynamic
  5. Itérer à travers la boucle interne pour chaque mois
  6. Itérer à travers la boucle externe pour chaque année.