MySQL Crosstab in a view

Question

I have the following data.

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

Is there a way to create a cross table in a view. The Fields column contains the field headings, and each respective field should have its associated value, but with 1 record ID. So in the example RecordID 1072 should be rolled up to one row.

Sure there is a way! It's a matter of adding a join for each column, and tying everything together by RecordID — bfavaretto, Aug 07 '12 at 16:35
What value is associated with each field? You have 3 recvalue fields so which one of those fields is equal to each record? — Taryn, Aug 07 '12 at 16:44

score 1 · Accepted Answer · answered Aug 07 '12 at 16:56

Part of your problem is your table structure. Unfortunately MySQL does not have a PIVOT/UNPIVOT function so you will need to do this with a UNION ALL and then CASE statements:

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

See SQL Fiddle with Demo

This query takes all of the values from your three recvalue columns and places it into one column so you can transform those values into your single row of data.

Is there a way this could be written for a view. I don;t think you ca have a subquery in the from clause in a view — user1236443, Aug 07 '12 at 16:58
I believe you could create a view of the subquery and then query that one. So create a view of the `UNION ALL` query and then you can select from that. — Taryn, Aug 07 '12 at 17:02

MySQL Crosstab in a view

1 Answers1