MS SQL Server
  1. MS SQL Server
  3. SQL Server: merge des sous-requêtes sans doublon
Intereting Posts
Type de text SQL Server par rapport au type de données varchar SQL Server Unique Identitifier vs Integer convertir la valeur nulle de la string en int Appel de la procédure stockée VBA Ordre par entier avec des champs vides en bas Comment get les informations de la source de données à partir d'un rapport SSRS, en utilisant .NET Comment une instruction delete peut-elle entrer en conflit avec une contrainte REFERENCE lorsqu'il n'y a pas d'loggings correspondants dans la table référencée? Utilisation du mot key "and" dans la fonction containstable Comment puis-je savoir combien de connections SQL j'ai ouvert dans un service Windows? Comment je reçois la similitude des phrases en C # .NET Pourquoi le server SQL possède-t-il à la fois l'authentification de la couche de database et du server (Login et User) mais Oracle n'a d'user que celui de l'user? Comment recevoir des notifications d'emploi par courrier électronique? La connection IIS a échoué pour l'user 'NT AUTHORITY \ ANONYMOUS LOGON' MVC4: UserIsInRole – Impossible de se connecter à la database SQL Server Sur Quelles opérations les index ne fonctionnent pas bien ou ne sont pas utilisés

SQL Server: merge des sous-requêtes sans doublon

Je veux merge ce résultat en 2 lignes, de toute façon pour le faire? Merci d'avance !

Résultat actuel: 

cust-no document order-no Black Description Black CR Yellow Description Yellow CR CE074L00 10012107 0 NULL NULL 841437 P.CART YLW C3501S; -5 CE074L00 10012107 0 NULL NULL 841696 P.CART YLW C5502S; -7 CE074L00 10012107 0 841436 P.CART BLK C3501S; -8 NULL NULL CE074L00 10012107 0 841695 P.CART BLK C5502S; -3 NULL NULL

Résultat attendu : 

 cust-no document order-no Black Description Black CR Yellow Description Yellow CR CE074L00 10012107 0 841436 P.CART BLK C3501S; -8 841437 P.CART YLW C3501S; -5 CE074L00 10012107 0 841436 P.CART BLK C3501S; -3 841696 P.CART YLW C5502S; -7

La requête SQL actuelle: 

 select a.[cust-no], a.[document],a.[order-no], a.[Black Description],a.[Black CR],a.[Yellow Description],a.[Yellow CR] from (select i1.[cust-no], i1.[document], i1.[order-no], il1.[description] [Black Description], il1.[qty-shipped] [Black CR], null [Yellow Description], null [Yellow CR] from invoice i1 inner join [invoice-line] il1 on il1.[document] = i1.[document] inner join toner t on t.[edp code] = il1.[item-no] and t.[color] = 'black' and i1.[dbill-type] = 'PS' and i1.[invoice-date] > '2015-01-01' and i1.[order-code] = 'FOCA' and i1.[cust-no] = 'CE074L00' union select i1.[cust-no], i1.[document], i1.[order-no], null [Black Description], null [Black CR], il1.[description] [Yellow Description],il1.[qty-shipped] [Yellow CR] from invoice i1 inner join [invoice-line] il1 on il1.[document] = i1.[document] inner join toner t on t.[edp code] = il1.[item-no] and t.[color] = 'yellow' and i1.[dbill-type] = 'PS' and i1.[invoice-date] > '2015-01-01' and i1.[order-code] = 'FOCA' and i1.[cust-no] = 'CE074L00') a

La requête modifiée ne montre cependant qu'une seule ligne, ne sachant pas comment utiliser RowNo : 

 SELECT a.[cust-no], a.[document], a.[order-no], MAX(CASE WHEN a.[color] = 'black' THEN a.[description] END) AS [Black Description], MAX(CASE WHEN a.[color] = 'black' THEN a.[qty-shipped] END) AS [Black CR], MAX(CASE WHEN a.[color] = 'yellow' THEN a.[description] END) AS [Yellow Description], MAX(CASE WHEN a.[color] = 'yellow' THEN a.[qty-shipped] END) AS [Yellow CR] FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY i1.[cust-no],i1.[document],i1.[order-no] ORDER BY i1.[cust-no] desc) AS RowNo, i1.[cust-no],i1.[document],i1.[order-no],t.[color],il1.[description],il1.[qty-shipped] FROM invoice i1 INNER JOIN [invoice-line] il1 ON il1.[document] = i1.[document] INNER JOIN toner t ON t.[edp code] = il1.[item-no] WHERE t.[color] IN('black', 'yellow') and i1.[dbill-type] = 'PS' and i1.[invoice-date] > '20150101' and i1.[order-code] = 'FOCA' and i1.[cust-no] = 'CE074L00' ) AS a GROUP BY a.[cust-no], a.[document], a.[order-no]

Résultat de la requête modifiée: 

 cust-no document order-no Black Description Black CR Yellow Description Yellow CR CE074L00 10012107 0 841695 P.CART BLK C5502S; -3 841696 P.CART YLW C5502S; -5

Données à tester: 

 create table #Invoice( [document] int, [cust-no] varchar(15), [order-no] int, [dbill-type] varchar(15), [invoice-date] datetime, [order-code] varchar(15)) create table #Invoice_line( [document] int, [item-no] int, [description] varchar(100), [qty-shipped] int) create table #toner( [edp code] int, [color] varchar(15)) insert into #invoice values (10012107,'CE074L00',0,'PS','2015-03-01','FOCA') insert into #Invoice_line values (10012107,841436,'841436 P.CART BLK C3501S;',-8) insert into #Invoice_line values (10012107,841695,'841695 P.CART BLK C5502S;',-3) insert into #Invoice_line values (10012107,841437,'841437 P.CART YLW C3501S;',-5) insert into #Invoice_line values (10012107,841696,'841696 P.CART YLW C5502S;',-7) insert into #toner values(841436,'black') insert into #toner values(841695,'black') insert into #toner values(841437,'yellow') insert into #toner values(841696,'yellow')

Requête à tester: 

 SELECT a.[cust-no], a.[document], a.[order-no], MAX(CASE WHEN a.[color] = 'black' THEN a.[description] END) AS [Black Description], MAX(CASE WHEN a.[color] = 'black' THEN a.[qty-shipped] END) AS [Black CR], MAX(CASE WHEN a.[color] = 'yellow' THEN a.[description] END) AS [Yellow Description], MAX(CASE WHEN a.[color] = 'yellow' THEN a.[qty-shipped] END) AS [Yellow CR] FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY i1.[cust-no],i1.[document],i1.[order-no] ORDER BY i1.[cust-no] desc) AS RowNo, i1.[cust-no],i1.[document],i1.[order-no],t.[color],il1.[description],il1.[qty-shipped] FROM #invoice i1 INNER JOIN #invoice_line il1 ON il1.[document] = i1.[document] INNER JOIN #toner t ON t.[edp code] = il1.[item-no] WHERE t.[color] IN('black', 'yellow') and i1.[dbill-type] = 'PS' and i1.[invoice-date] > '20150101' and i1.[order-code] = 'FOCA' and i1.[cust-no] = 'CE074L00' ) AS a GROUP BY a.[cust-no], a.[document], a.[order-no]

@OP, Il semble que cela fonctionne très bien, corrigez-moi si je me trompe. 

 SELECT a.[cust-no] , a.[document] , a.[order-no] , MAX(CASE WHEN a.[color] = 'black' THEN a.[description] END) AS [Black Description] , MAX(CASE WHEN a.[color] = 'black' THEN a.[qty-shipped] END) AS [Black CR] , MAX(CASE WHEN a.[color] = 'yellow' THEN a.[description] END) AS [Yellow Description] , MAX(CASE WHEN a.[color] = 'yellow' THEN a.[qty-shipped] END) AS [Yellow CR] FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY i1.[cust-no], i1.[document], i1.[order-no], t.[color] ORDER BY i1.[cust-no] DESC) AS RowNo , i1.[cust-no] , i1.[document] , i1.[order-no] , t.[color] , il1.[description] , il1.[qty-shipped] FROM #invoice i1 INNER JOIN #invoice_line il1 ON il1.[document] = i1.[document] INNER JOIN #toner t ON t.[edp code] = il1.[item-no] WHERE t.[color] IN ('black', 'yellow') AND i1.[dbill-type] = 'PS' AND i1.[invoice-date] > '20150101' AND i1.[order-code] = 'FOCA' AND i1.[cust-no] = 'CE074L00' ) AS a GROUP BY a.[cust-no] , a.[document] , a.[order-no] , a.[RowNo]

Vous pouvez utiliser l' case-based aggregation ou l' conditional aggregation : 

 SELECT i1.[cust-no], i1.[document], i1.[order-no], MAX(CASE WHEN t.[color] = 'black' THEN il1.[description] END) AS [Black Description], MAX(CASE WHEN t.[color] = 'black' THEN il1.[qty-shipped] END) AS [Black CR], MAX(CASE WHEN t.[color] = 'yellow' THEN il1.[description] END) AS [Yellow Description], MAX(CASE WHEN t.[color] = 'yellow' THEN il1.[qty-shipped] END) AS [Yellow CR] FROM invoice i1 INNER JOIN [invoice-line] il1 ON il1.[document] = i1.[document] INNER JOIN toner t ON t.[edp code] = il1.[item-no] WHERE t.[color] IN('black', 'yellow') and i1.[dbill-type] = 'PS' and i1.[invoice-date] > '20150101' and i1.[order-code] = 'FOCA' and i1.[cust-no] = 'CE074L00' GROUP BY i1.[cust-no], i1.[document], i1.[order-no]

Evaldas a raison. Lorsque vous ajoutez Color à votre clause PARTITION BY, vous obtenez le partitionnement que vous searchz: 

 RowNo cust-no document order-no color description qty-shipped 1 CE074L00 10012107 0 black 841436 P.CART BLK C3501S; -8 2 CE074L00 10012107 0 black 841695 P.CART BLK C5502S; -3 1 CE074L00 10012107 0 yellow 841437 P.CART YLW C3501S; -5 2 CE074L00 10012107 0 yellow 841696 P.CART YLW C5502S; -7

Lorsque vous ajoutez le RowNo à la clause GROUP BY, vous obtenez deux lignes au lieu d'une, car il est désormais également regroupé sur le RowNo. 

 cust-no document order-no Black Description Black CR Yellow Description Yellow CR CE074L00 10012107 0 841436 P.CART BLK C3501S; -8 841437 P.CART YLW C3501S; -5 CE074L00 10012107 0 841695 P.CART BLK C5502S; -3 841696 P.CART YLW C5502S; -7