MS SQL Server
  1. MS SQL Server
  3. Essayer de merge des lignes en une seule ligne avec certaines conditions
Intereting Posts
OpenQuery à DB2 / AS400 à partir de SQL Server 2000 provoquant des verrous Optimisation de la procédure stockée avec plusieurs "LIKE" Terminez gracieusement une requête SQL Server longue durée à partir d'ADO.NET Insertion de la valeur NaN dans une colonne flottante Sélectionnez Colonnes en fonction de la valeur d'une autre variable? Cross Apply donne des valeurs nulles Trier la date de la semaine en format de string Comment traduire l'instruction CASE nestede dans la clause WHERE Comment faire pour que les valeurs d'une table SQL soient les en-têtes d'une vue? Comment afficher une list de checkbox basée sur un DropDownList en utilisant Script dans ASP MVC Trouver le dernier logging inséré dans SQL qui ni l'id ni le champ de date mssql_connect (): Impossible de se connecter au server (sans freetds.conf) Une manière plus élégante d'agréger des colonnes Manière rapide de comparer les différences entre les loggings SQL ordonnés Supprimer la requête ne fonctionne pas dans SQL Server

Essayer de merge des lignes en une seule ligne avec certaines conditions

Étant donné que 2 lignes ou plus sont sélectionnées pour merge, l'une d'entre elles est identifiée comme étant la ligne du model. Les autres lignes doivent merge leurs données dans toutes les colonnes de valeurs nulles que le model a.

Exemple de données: 

Id Name Address City State Active Email Date 1 Acme1 NULL NULL NULL NULL [email protected] 3/1/2011 2 Acme1 1234 Abc Rd Springfield OR 0 [email protected] 1/12/2012 3 Acme2 NULL NULL NULL 1 [email protected] 4/19/2012

Supposons qu'un user a choisi la ligne avec l'ID 1 comme ligne de model et les lignes avec les ID 2 et 3 doivent être fusionnées dans la ligne 1, puis supprimées. Toute colonne de valeur nulle dans la ligne Id 1 doit être remplie (si elle existe) de la valeur non nulle la plus récente (voir colonne Date), et les valeurs non nulles déjà présentes dans la ligne Id 1 doivent être laissées telles quelles. Le résultat de cette requête sur datatables ci-dessus devrait être exactement ceci: 

 Id Name Address City State Active Email Date 1 Acme1 1234 Abc Road Springfield OR 1 [email protected] 3/1/2011

Notez que la valeur Active est 1 et non 0 car la ligne Id 3 a la date la plus récente.

PS Aussi, est-il possible de le faire sans définir explicitement / savoir à l'avance ce que sont tous les noms de colonnes? La table avec laquelle je travaille a une tonne de colonnes, avec de nouvelles colonnes ajoutées tout le time. Existe-t-il un moyen de searchr tous les noms de colonnes dans la table, puis d'utiliser cette sous-requête ou tenttable pour faire le travail?

Vous pouvez le faire en ordonnant les lignes d'abord par un indicateur de model, puis par date desc. La ligne du model doit toujours être la dernière. Chaque rangée reçoit un numéro dans cet ordre. En utilisant max (), nous trouvons une cellule occupée par le poing (dans l'ordre décroissant des nombres). Ensuite, nous sélectionnons les colonnes des lignes correspondant à ces maximums. 

 ; with rows as ( select test.*, -- Template row must be last - how do you decide which one is template row? -- In this case template row is the one with id = 1 row_number() over (order by case when id = 1 then 1 else 0 end, date) rn from test -- Your list of rows to merge goes here -- where id in ( ... ) ), -- Finding first occupied row per column positions as ( select max (case when Name is not null then rn else 0 end) NamePosition, max (case when Address is not null then rn else 0 end) AddressPosition, max (case when City is not null then rn else 0 end) CityPosition, max (case when State is not null then rn else 0 end) StatePosition, max (case when Active is not null then rn else 0 end) ActivePosition, max (case when Email is not null then rn else 0 end) EmailPosition, max (case when Date is not null then rn else 0 end) DatePosition from rows ) -- Finally join this columns in one row select (select Name from rows cross join Positions where rn = NamePosition) name, (select Address from rows cross join Positions where rn = AddressPosition) Address, (select City from rows cross join Positions where rn = CityPosition) City, (select State from rows cross join Positions where rn = StatePosition) State, (select Active from rows cross join Positions where rn = ActivePosition) Active, (select Email from rows cross join Positions where rn = EmailPosition) Email, (select Date from rows cross join Positions where rn = DatePosition) Date from test -- Any id will suffice, or even DISTINCT where id = 1

Vous pourriez le vérifier à Sql Fiddle .

MODIFIER:

Les jointures croisées dans la dernière section peuvent en fait être des jointures internes sur rows.rn = xxxPosition. Cela fonctionne de cette façon, mais le changement de jointure interne serait une amélioration.

Ce n'est pas si compliqué.

Au début .. DECLARE @templateID INT = 1 .. vous pouvez vous callbacker quelle ligne est traitée comme model ..

Trouvez maintenant les dernières valeurs NOT NULL (excluez la ligne du template). Le plus simple est d'utiliser les sous-requêtes TOP 1 pour chaque colonne: 

 SELECT (SELECT TOP 1 Name FROM DataTab WHERE Name IS NOT NULL AND NOT ID = @templateID ORDER BY Date DESC) AS LatestName, (SELECT TOP 1 Address FROM DataTab WHERE Address IS NOT NULL AND NOT ID = @templateID ORDER BY Date DESC) AS AddressName -- add more columns here

Enveloppez ci-dessus dans CTE (Common Table Expression) de sorte que vous avez une bonne input pour votre UDPATE .. 

 WITH Latest_CTE (CTE_LatestName, CTE_AddressName) -- add more columns here; I like CTE prefix to distinguish source columns from target columns.. AS -- Define the CTE query. ( SELECT (SELECT TOP 1 Name FROM DataTab WHERE Name IS NOT NULL AND NOT ID = @templateID ORDER BY Date DESC) AS LatestName, (SELECT TOP 1 Address FROM DataTab WHERE Address IS NOT NULL AND NOT ID = @templateID ORDER BY Date DESC) AS AddressName -- add more columns here ) UPDATE <update statement here (below)>

Maintenant, faites une mise à jour intelligente de votre ligne de model en utilisant ISNULL – elle agira comme une mise à jour conditionnelle – mise à jour seulement si la colonne cible est nulle 

 WITH <common expression statement here (above)> UPDATE DataTab SET Name = ISNULL(Name, CTE_LatestName), -- if Name is null then set Name to CTE_LatestName else keep Name as Name Address = ISNULL(Address, CTE_LatestAddress) -- add more columns here.. WHERE ID = @templateID

Et la dernière tâche est de supprimer les lignes autres que la ligne du model. 

 DELETE FROM DataTab WHERE NOT ID = @templateID

Clair?

Pour les colonnes dynamics, vous devez écrire une solution en utilisant SQL dynamic.

Vous pouvez interroger sys.columns et sys.tables pour get la list des colonnes dont vous avez besoin, puis vous voulez faire une boucle une fois pour chaque colonne null trouvant la première ligne non nulle pour cette colonne et mettre à jour votre ligne de sortie pour cette colonne. Une fois que vous arrivez à 0 dans la boucle, vous avez une ligne complète que vous pouvez ensuite afficher à l'user.

Je devrais faire attention aux dates d'affichage. Dans tous les cas, voici une solution utilisant SQL dynamic pour build une instruction de mise à jour. Cela devrait vous donner quelque chose à build, de toute façon.

Il y a du code supplémentaire pour valider les résultats en cours de route, mais j'ai essayé de commenter d'une manière qui rendait ce code non vital apparent. 

 CREATE TABLE dbo.Dummy ( [ID] int , [Name] varchar(30), [Address] varchar(40) null, [City] varchar(30) NULL, [State] varchar(2) NULL, [Active] tinyint NULL, [Email] varchar(30) NULL, [Date] date NULL ); -- INSERT dbo.Dummy VALUES ( 1, 'Acme1', NULL, NULL, NULL, NULL, '[email protected]', '3/1/2011' ) , ( 2, 'Acme1', '1234 Abc Rd', 'Springfield', 'OR', 0, '[email protected]', '1/12/2012' ) , ( 3, 'Acme2', NULL, NULL, NULL, 1, '[email protected]', '4/19/2012' ); DECLARE @TableName nvarchar(128) = 'Dummy', @TemplateID int = 1, @SetStmtList nvarchar(max) = '', @LoopCounter int = 0, @ColumnCount int = 0, @SQL nvarchar(max) = '' ; -- --Create a table to hold the column names DECLARE @ColumnList table ( ColumnID tinyint IDENTITY, ColumnName nvarchar(128) ); -- --Get the column names INSERT @ColumnList ( ColumnName ) SELECT c.name FROM sys.columns AS c JOIN sys.tables AS t ON t.object_id = c.object_id WHERE t.name = @TableName; -- --Create loop boundaries to build out the SQL statement SELECT @ColumnCount = MAX( l.ColumnID ), @LoopCounter = MIN (l.ColumnID ) FROM @ColumnList AS l; -- --Loop over the column names WHILE @LoopCounter <= @ColumnCount BEGIN --Dynamically construct SET statements for each column except ID (See the WHERE clause) SELECT @SetStmtList = @SetStmtList + ',' + l.ColumnName + ' =COALESCE(' + l.ColumnName + ', (SELECT TOP 1 ' + l.ColumnName + ' FROM ' + @TableName + ' WHERE ' + l.ColumnName + ' IS NOT NULL AND ID <> ' + CAST(@TemplateID AS NVARCHAR(MAX )) + ' ORDER BY Date DESC)) ' FROM @ColumnList AS l WHERE l.ColumnID = @LoopCounter AND l.ColumnName <> 'ID'; -- SELECT @LoopCounter = @LoopCounter + 1; -- END; --TESTING - Validate the initial table values SELECT * FROM dbo.Dummy ; -- --Get rid of the leading common in the SetStmtList SET @SetStmtList = SUBSTRING( @SetStmtList, 2, LEN( @SetStmtList ) - 1 ); --Build out the rest of the UPDATE statement SET @SQL = 'UPDATE ' + @TableName + ' SET ' + @SetStmtList + ' WHERE ID = ' + CAST(@TemplateID AS NVARCHAR(MAX )) --Then execute the update EXEC sys.sp_executesql @SQL; -- --TESTING - Validate the updated table values SELECT * FROM dbo.Dummy ; -- --Build out the DELETE statement SET @SQL = 'DELETE FROM ' + @TableName + ' WHERE ID <> ' + CAST(@TemplateID AS NVARCHAR(MAX )) --Execute the DELETE EXEC sys.sp_executesql @SQL; -- --TESTING - Validate the final table values SELECT * FROM dbo.Dummy; -- DROP TABLE dbo.Dummy;