View 中的 MySQL 交叉表

Question

我有以下数据。

RecordID    |RecValue1  |RecValue2  |RecValue3              |Fields
1072        |130227     |0          |                       |Document_Number
1072        |1241388    |0          |                       |Supplier_Number
1072        |20008968   |0          |                       |Invoice_Number
1072        |           |0          |1995-04-21 00:00:00    |Invoice_Date
1072        |           |0          |1995-04-23 00:00:00    |Posting_Date
1072        |Invoice    |0          |                       |Document_Type
1072        |           |0          |1995-05-12 17:46:32    |Paid_Date
1072        |F609       |0          |                       |Entry_ID

有没有办法在 View 中创建交叉表。字段列包含字段标题，每个字段都应该有其关联的值，但有 1 个记录 ID。因此，在示例中，RecordID 1072 应汇总为一行。

Answer 1

您的问题的一部分是您的表结构。不幸的是，MySQL 没有 PIVOT/UNPIVOT 函数，因此您需要使用 UNION ALL 和 CASE 语句来执行此操作:

SELECT RecordId,
  MAX(CASE WHEN Fields = 'Document_Number' THEN recvalue END)  Document_Number,
  MAX(CASE WHEN Fields = 'Supplier_number' THEN recvalue END)  Supplier_number,
  MAX(CASE WHEN Fields = 'Invoice_number'  THEN recvalue END)  Invoice_number,
  MAX(CASE WHEN Fields = 'Invoice_Date' THEN recvalue END)  Invoice_Date,
  MAX(CASE WHEN Fields = 'Posting_Date' THEN recvalue END)  Posting_Date,
  MAX(CASE WHEN Fields = 'Document_type' THEN recvalue END)  Document_type,
  MAX(CASE WHEN Fields = 'Paid_Date' THEN recvalue END)  Paid_Date,
  MAX(CASE WHEN Fields = 'Entry_ID' THEN recvalue END)  Entry_ID
FROM
(
  SELECT RecordId, RecValue1 RecValue, fields
  FROM test
  UNION ALL
  SELECT RecordId, RecValue2 RecValue,fields
  FROM test
  UNION ALL
  SELECT RecordId, RecValue3 RecValue, fields
  FROM test
) unpvt
GROUP BY RecordId

参见SQL Fiddle with Demo

此查询获取三个recvalue列中的所有值并将其放入一列中，以便您可以将这些值转换为单行数据。