由CSV文件创建的枢轴表

Question

我正在处理一个问题，即我们要导入的CSV文件上的格式需要“枢纽”，以匹配我们用于处理导入的程序所需的格式。

目前，我们正在进口以下格式附带的文件：

帐户	部门	Jun2022	2月2022年	Mar2022
12345	销售	$ 456	$ 456 $ 876	$ 345
98765	HR	$ 765	$ 345 $ 345	$ 344

我们需要该格式来持有一列的时间段，每列时间都会使每个帐户重复每次重复每个帐户， 。例如：

帐户	部门	周期	金额
12345	销售	JAN2022	$ 456
12345	销售	2月2022年	$ 876
12345	销售	MAR2022	$ 345

我们使用JavaScript导入此CSV，但是其基本JS，因为该程序不支持JQuery或任何其他JS库。一旦我们使用JS将表导入阶段区域，我们也可以使用SQL修改数据，因此可以使用JS或SQL解决。

我们正在使用CSV来数组函数来读取用于导入分期的CSV文件：

function CSVToArray(strData, strDelimiter) {
  // Check to see if the delimiter is defined. If not, then default to comma.
  strDelimiter = strDelimiter || ",";

  // Create a regular expression to parse the CSV values.
  var objPattern = new RegExp(
    // Delimiters.
    "(\\" +
      strDelimiter +
      "|\\r?\\n|\\r|^)" +
      // Quoted fields.
      '(?:"([^"]*(?:""[^"]*)*)"|' +
      // Standard fields.
      '([^"\\' +
      strDelimiter +
      "\\r\\n]*))",
    "gi"
  );

  // Create an array to hold our data. Give the array a default empty first row.
  var arrData = [[]];

  // Create an array to hold our individual pattern matching groups.
  var arrMatches = null;

  // Keep looping over the regular expression matches until we can no longer find a match.
  while ((arrMatches = objPattern.exec(strData))) {
    // Get the delimiter that was found.
    var strMatchedDelimiter = arrMatches[1];

    // Check to see if the given delimiter has a length (is not the start of string) and if it matches
    // field delimiter. If id does not, then we know that this delimiter is a row delimiter.
    if (strMatchedDelimiter.length && strMatchedDelimiter !== strDelimiter) {
      // Since we have reached a new row of data, add an empty row to our data array.
      arrData.push([]);
    }

    //Now that we have our delimiter out of the way, let's check to see which kind of value we captured (quoted or unquoted).
    var strMatchedValue;

    if (arrMatches[2]) {
      // We found a quoted value. When we capture this value, unescape any double quotes.
      strMatchedValue = arrMatches[2]
        .replace(new RegExp('""', "g"), '"')
        .replace('"', "");
    } else {
      // We found a non-quoted value.
      strMatchedValue = arrMatches[3];
    }
    // Now that we have our value string, let's add it to the data array.
    arrData[arrData.length - 1].push(strMatchedValue);
  }
  // Return the parsed data.
  return arrData;
}

Answer 1

undivot应该为您工作：

/* sample data */
with t as
 (select '12345' account,
         'Sales' department,
         '$456' jan2022,
         '$876' feb2022,
         '$345' mar2022
    from dual
  union all
  select '98765' account,
         'HR' department,
         '$765' jan2022,
         '$345' feb2022,
         '$344' mar2022
    from dual)

select *
  from t
 unpivot include nulls(amount for period in(jan2022 as 'jan2022',
                                            feb2022 as 'feb2022',
                                            mar2022 as 'mar2022'));

如果您有动态列，您将对这种闲暇时间有糟糕的时光 - 您必须独自生成“ novivot in Crause”（与JAN2022为'JAN2022'的那部分）。