Astuces Excel
  1. Astuces Excel
  3. Répliquer les lignes dans une table par les colonnes
Intereting Posts
Comment get des fonctions définies par l'user dans Excel Add-Ins (Excel 2007) pour fonctionner avec AutoComplete? Ajout de la bordure à la dernière ligne utilisée via VBA? EPPlus: le tableau de destination n'était pas assez long. Vérifiez l'indice et la longueur, et les limites inférieures du tableau Importation de données Excel dans plusieurs tables SQL Calculer à partir de 2 tables pivotantes Excel VBA – Changez la visibilité du button en fonction de la mise à jour vers une cellule adjacente Copier le résultat trouvé dans la ligne actuelle Empêcher Excel d'activer de nouveaux objects (feuilles, charts, classurs) Y a-t-il une différence entre VBA.Left () et Left ()? Coller des valeurs d'une feuille de calcul à une autre Si X = Vrai VSTO: get des propriétés de cellules Excel dans "en vrac" S3 path imprimé incorrectement par le lecteur Excel Catégorisation des données d'admission de l'hôpital dans Excel ou R "Erreur d'exécution 1004 incapable de définir la propriété cachée de la class de plage" lorsque vous essayez de cacher / afficher certaines colonnes et que la feuille est protégée Comptez toutes les valeurs de cellule dans plusieurs colonnes qui correspondent à un certain motif dans Excel

Répliquer les lignes dans une table par les colonnes

J'ai besoin de développer un rapport, je le crée dans Excel, mais il est devenu si lourd que même mon PC ne peut pas l'ouvrir. En ce moment, je décide de le créer avec SQL. L'input excel est quelque chose comme ceci: 

Service_order PENDING_DAYS SERVICE_TYPE ASC code INOUTWTY Part_code1 Part_code2 Part_code3 Part_code4 Part_code5 4182864919 18 CI 3440690 LP GH82-11218A GH96-09406A GH81-13594A GH02-11552A GH02-11553A 4182868153 18 CI 4285812 LP GH97-17670B 4182929636 17 CI 4276987 LP GH97-17260C GH02-10203A 4182953067 16 CI 3440690 LP GH97-17940C 4182954688 16 CI 6195657 LP GH82-10555A GH97-17852A GH81-13071A 4182955036 16 PS 6195657 LP GH97-17940C

et le résultat en utilisant ces codes 

 =HLOOKUP(Sheet3!A$1;Sheet3!$A$1:$F$10000;CEILING((ROW()-ROW(Sheet3!$A$1))/5+1;1);FALSE)" =OFFSET(WholePart;TRUNC((ROW()-ROW($G$2))/COLUMNS(WholePart));MOD(ROW()-ROW($G$2);COLUMNS(WholePart));1;1)

* WholePart est une valeur partCode.

sont comme ceci: entrez la description de l'image ici

Ce que je veux faire, c'est de convertir ces formules ou d'avoir une sortie comme celle-ci. Appréciez-le.

Mon conseil consiste à préparer vos données dans EXCEL et à charger dans des arrays normalisés. Ensuite, vous pouvez get le résultat en joignant les arrays. 

 create table T1( Service_order bigint primary key, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10) ); create table T2( Service_order bigint, Part_code varchar(50) ); insert into T1(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY) values (4182864919 , 18,'CI',3440690,'LP'), (4182868153 , 18,'CI',4285812,'LP'), (4182929636 , 17,'CI',4276987,'LP'), (4182953067 , 16,'CI',3440690,'LP'), (4182954688 , 16,'CI',6195657,'LP'), (4182955036 , 16,'PS',6195657,'LP'); insert into T2(Service_order, Part_code) values (4182864919,'GH82-11218A'), (4182864919,'GH96-09406A'), (4182864919,'GH81-13594A'), (4182864919,'GH02-11552A'), (4182864919,'GH02-11553A'), (4182868153,'GH97-17670B'), (4182929636,'GH97-17260C'), (4182929636,'GH02-10203A'), (4182953067,'GH97-17940C'), (4182954688,'GH82-10555A'), (4182954688,'GH97-17852A'), (4182954688,'GH81-13071A'), (4182955036,'GH97-17940C') select T1.*, T2.Part_code from T1 join T2 on T1.Service_order = T2.Service_order order by T1.Service_order, T2.Part_code; ); create table T1( Service_order bigint primary key, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10) ); create table T2( Service_order bigint, Part_code varchar(50) ); insert into T1(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY) values (4182864919 , 18,'CI',3440690,'LP'), (4182868153 , 18,'CI',4285812,'LP'), (4182929636 , 17,'CI',4276987,'LP'), (4182953067 , 16,'CI',3440690,'LP'), (4182954688 , 16,'CI',6195657,'LP'), (4182955036 , 16,'PS',6195657,'LP'); insert into T2(Service_order, Part_code) values (4182864919,'GH82-11218A'), (4182864919,'GH96-09406A'), (4182864919,'GH81-13594A'), (4182864919,'GH02-11552A'), (4182864919,'GH02-11553A'), (4182868153,'GH97-17670B'), (4182929636,'GH97-17260C'), (4182929636,'GH02-10203A'), (4182953067,'GH97-17940C'), (4182954688,'GH82-10555A'), (4182954688,'GH97-17852A'), (4182954688,'GH81-13071A'), (4182955036,'GH97-17940C') select T1.*, T2.Part_code from T1 join T2 on T1.Service_order = T2.Service_order order by T1.Service_order, T2.Part_code; ); create table T1( Service_order bigint primary key, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10) ); create table T2( Service_order bigint, Part_code varchar(50) ); insert into T1(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY) values (4182864919 , 18,'CI',3440690,'LP'), (4182868153 , 18,'CI',4285812,'LP'), (4182929636 , 17,'CI',4276987,'LP'), (4182953067 , 16,'CI',3440690,'LP'), (4182954688 , 16,'CI',6195657,'LP'), (4182955036 , 16,'PS',6195657,'LP'); insert into T2(Service_order, Part_code) values (4182864919,'GH82-11218A'), (4182864919,'GH96-09406A'), (4182864919,'GH81-13594A'), (4182864919,'GH02-11552A'), (4182864919,'GH02-11553A'), (4182868153,'GH97-17670B'), (4182929636,'GH97-17260C'), (4182929636,'GH02-10203A'), (4182953067,'GH97-17940C'), (4182954688,'GH82-10555A'), (4182954688,'GH97-17852A'), (4182954688,'GH81-13071A'), (4182955036,'GH97-17940C') select T1.*, T2.Part_code from T1 join T2 on T1.Service_order = T2.Service_order order by T1.Service_order, T2.Part_code;

MODIFIER

Alternativement, vous pouvez charger des données EXCEL originales (premier tableau) et les normaliser en SQL. 

 -- create normalized tables create table T1( Service_order bigint primary key, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10) ); create table T2( Service_order bigint, Part_code varchar(50) ); -- load data from excel. create table excelData( Service_order bigint, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10), Part_code1 varchar(50), Part_code2 varchar(50), Part_code3 varchar(50), Part_code4 varchar(50), Part_code5 varchar(50) ); -- Below i use sample data insert instead of load. insert into excelData(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY ,Part_code1, Part_code2, Part_code3, Part_code4, Part_code5) values (4182864919 , 18,'CI',3440690,'LP','GH82-11218A','GH96-09406A','GH81-13594A','GH02-11552A','GH02-11553A'), (4182868153 , 18,'CI',4285812,'LP','GH97-17670B','','','',''), (4182929636 , 17,'CI',4276987,'LP','GH97-17260C','GH02-10203A','','',''), (4182953067 , 16,'CI',3440690,'LP','GH97-17940C','','','',''), (4182954688 , 16,'CI',6195657,'LP','GH82-10555A','GH97-17852A','GH81-13071A','',''), (4182955036 , 16,'PS',6195657,'LP','GH97-17940C','','','',''); -- store loaded data into normalized tables. insert into T1(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY) select Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY from excelData; insert into T2(Service_order, Part_code) select Service_order, Part_code from excelData cross apply ( --unpivot select Part_code1 as Part_code where len(Part_code1) > 0 union all select Part_code2 where len(Part_code2) > 0 union all select Part_code3 where len(Part_code3) > 0 union all select Part_code4 where len(Part_code4) > 0 union all select Part_code5 where len(Part_code5) > 0 ) unp; -- check it select * from T1; select * from T2; ); -- create normalized tables create table T1( Service_order bigint primary key, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10) ); create table T2( Service_order bigint, Part_code varchar(50) ); -- load data from excel. create table excelData( Service_order bigint, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10), Part_code1 varchar(50), Part_code2 varchar(50), Part_code3 varchar(50), Part_code4 varchar(50), Part_code5 varchar(50) ); -- Below i use sample data insert instead of load. insert into excelData(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY ,Part_code1, Part_code2, Part_code3, Part_code4, Part_code5) values (4182864919 , 18,'CI',3440690,'LP','GH82-11218A','GH96-09406A','GH81-13594A','GH02-11552A','GH02-11553A'), (4182868153 , 18,'CI',4285812,'LP','GH97-17670B','','','',''), (4182929636 , 17,'CI',4276987,'LP','GH97-17260C','GH02-10203A','','',''), (4182953067 , 16,'CI',3440690,'LP','GH97-17940C','','','',''), (4182954688 , 16,'CI',6195657,'LP','GH82-10555A','GH97-17852A','GH81-13071A','',''), (4182955036 , 16,'PS',6195657,'LP','GH97-17940C','','','',''); -- store loaded data into normalized tables. insert into T1(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY) select Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY from excelData; insert into T2(Service_order, Part_code) select Service_order, Part_code from excelData cross apply ( --unpivot select Part_code1 as Part_code where len(Part_code1) > 0 union all select Part_code2 where len(Part_code2) > 0 union all select Part_code3 where len(Part_code3) > 0 union all select Part_code4 where len(Part_code4) > 0 union all select Part_code5 where len(Part_code5) > 0 ) unp; -- check it select * from T1; select * from T2; ); -- create normalized tables create table T1( Service_order bigint primary key, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10) ); create table T2( Service_order bigint, Part_code varchar(50) ); -- load data from excel. create table excelData( Service_order bigint, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10), Part_code1 varchar(50), Part_code2 varchar(50), Part_code3 varchar(50), Part_code4 varchar(50), Part_code5 varchar(50) ); -- Below i use sample data insert instead of load. insert into excelData(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY ,Part_code1, Part_code2, Part_code3, Part_code4, Part_code5) values (4182864919 , 18,'CI',3440690,'LP','GH82-11218A','GH96-09406A','GH81-13594A','GH02-11552A','GH02-11553A'), (4182868153 , 18,'CI',4285812,'LP','GH97-17670B','','','',''), (4182929636 , 17,'CI',4276987,'LP','GH97-17260C','GH02-10203A','','',''), (4182953067 , 16,'CI',3440690,'LP','GH97-17940C','','','',''), (4182954688 , 16,'CI',6195657,'LP','GH82-10555A','GH97-17852A','GH81-13071A','',''), (4182955036 , 16,'PS',6195657,'LP','GH97-17940C','','','',''); -- store loaded data into normalized tables. insert into T1(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY) select Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY from excelData; insert into T2(Service_order, Part_code) select Service_order, Part_code from excelData cross apply ( --unpivot select Part_code1 as Part_code where len(Part_code1) > 0 union all select Part_code2 where len(Part_code2) > 0 union all select Part_code3 where len(Part_code3) > 0 union all select Part_code4 where len(Part_code4) > 0 union all select Part_code5 where len(Part_code5) > 0 ) unp; -- check it select * from T1; select * from T2; ); -- create normalized tables create table T1( Service_order bigint primary key, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10) ); create table T2( Service_order bigint, Part_code varchar(50) ); -- load data from excel. create table excelData( Service_order bigint, PENDING_DAYS int, SERVICE_TYPE varchar(10), ASC_code int, INOUTWTY varchar(10), Part_code1 varchar(50), Part_code2 varchar(50), Part_code3 varchar(50), Part_code4 varchar(50), Part_code5 varchar(50) ); -- Below i use sample data insert instead of load. insert into excelData(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY ,Part_code1, Part_code2, Part_code3, Part_code4, Part_code5) values (4182864919 , 18,'CI',3440690,'LP','GH82-11218A','GH96-09406A','GH81-13594A','GH02-11552A','GH02-11553A'), (4182868153 , 18,'CI',4285812,'LP','GH97-17670B','','','',''), (4182929636 , 17,'CI',4276987,'LP','GH97-17260C','GH02-10203A','','',''), (4182953067 , 16,'CI',3440690,'LP','GH97-17940C','','','',''), (4182954688 , 16,'CI',6195657,'LP','GH82-10555A','GH97-17852A','GH81-13071A','',''), (4182955036 , 16,'PS',6195657,'LP','GH97-17940C','','','',''); -- store loaded data into normalized tables. insert into T1(Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY) select Service_order, PENDING_DAYS, SERVICE_TYPE, ASC_code, INOUTWTY from excelData; insert into T2(Service_order, Part_code) select Service_order, Part_code from excelData cross apply ( --unpivot select Part_code1 as Part_code where len(Part_code1) > 0 union all select Part_code2 where len(Part_code2) > 0 union all select Part_code3 where len(Part_code3) > 0 union all select Part_code4 where len(Part_code4) > 0 union all select Part_code5 where len(Part_code5) > 0 ) unp; -- check it select * from T1; select * from T2;