1

I'm still very new here and I have a very limited knowledge in any sql query. I am helping my company to create a database to store "Painting Reports" using LibreOffice Base. I have create a table which has more than 12 columns/fields to store the report.

My current table:

+----------+----------+------------+-----------+-----------+---------+-----------------+
| ReportID | nthCoat  | DateCoat   | NameCoat  | Shade     | BatchNo | __more_fields__ |
+----------+----------+------------+-----------+-----------+---------+-----------------+
| 1        | 1st Coat | 01/01/2020 | Luxaprime | Red Oxide | B75489  | value           |
+----------+----------+------------+-----------+-----------+---------+-----------------+
| 1        | 2nd Coat | 02/01/2020 | Epilux    | Grey      | B98731  | value           |
+----------+----------+------------+-----------+-----------+---------+-----------------+
| 1        | 3rd Coat | 03/01/2020 | Luxol     | Yellow    | B78937  | value           |
+----------+----------+------------+-----------+-----------+---------+-----------------+

Since the table has too many columns/fields, it is not presentable in the generated report.

Hence, I would like to pivot the table to something like this:

+-----------------+------------+------------+------------+
| nthCoat         | 1st Coat   | 2nd Coat   | 3rd Coat   |
+-----------------+------------+------------+------------+
| DateCoat        | 01/01/2020 | 02/01/2020 | 03/01/2020 |
+-----------------+------------+------------+------------+
| NameCoat        | Luxaprime  | Epilux     | Luxol      |
+-----------------+------------+------------+------------+
| Shade           | Red Oxide  | Grey       | Yellow     |
+-----------------+------------+------------+------------+
| BatchNo         | B75489     | B98731     | B78937     |
+-----------------+------------+------------+------------+
| __more_fields__ | value      | value      | value      |
+-----------------+------------+------------+------------+

By pivoting the table, it can accommodate the numerous columns/fields and more presentable in report. The values of "nthCoat" are fixed to 1st, 2nd and 3rd Coat.

There is no calculation of values needed. All the values are mainly varchar fields.

Can someone please help me pivot the table? Thanks in advance.

I have spent hours and tried to search the site for any existing solution but most of them involve calculations with complex query which is too much for beginner like me to understand.

Albert
  • 11
  • 1
  • This question depends a lot on the capabilities of the database engine. Someone might be able to give an answer if you are using HSQLDB 2.5.x as external db with LibreOffice. – fredt Sep 12 '20 at 15:32
  • ho w is mysql realted to hsqldb, besides that you are trying to **unpivot** your table – nbk Sep 12 '20 at 16:37
  • See if this helps out: http://mysql.rjweb.org/doc.php/pivot – Rick James Sep 12 '20 at 19:22
  • @fredt at the moment i'm still using hsqldb as internal db. I'm willing switch to external db (i.e. mysql) if needed. – Albert Sep 12 '20 at 22:53
  • @nbk forgive my lack of experience. i have been searching online for solution. pivot is the term that I come across numerous times in my search, so i thought that is the key term to make it easier for others to understand. is there a better search term? – Albert Sep 12 '20 at 22:57
  • @RickJames thanks. i will study the material in the given link. – Albert Sep 12 '20 at 22:58
  • here is a mysql query without dynamic that produces your result https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=16ee7dd70e5448812bebc10925cf9eab , but i can't tell if this is useful. that is why i asked, how bit h are related, and yes it is a better search ter, – nbk Sep 12 '20 at 23:03
  • @Albert, the internal HSQLDB in LibreOffice is an old version. It can do this but the query becomes too complex. You can use the latest HSQLDB 2.5.1 as external db. This version has some new features that simplify writing this kind of complex query. – fredt Sep 13 '20 at 00:54
  • @nbk thanks for the tip. it is going to take awhile for me to study the query that you've provided. Seems like there are still a lot for me to learn. – Albert Sep 13 '20 at 08:29
  • @fredt i'll take a look at HSQLDB2.5.1. But I will try to work with MySQL first as nbk has given me a direction to look into. I've never thought that the query that i'm searching for is going to so complex. – Albert Sep 13 '20 at 08:35

0 Answers0