UNPIVOT syntax in Basex

[JohnJPDX]

I’m running HSQLDB. Trying to run an UNPIVOT but it doesn’t seem to recognize FOR as a function term. Here’s what I have.

SELECT Fairways, Course, Hole

FROM tblScores
UNPIVOT (Fairways FOR Hole IN (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18)

) AS Pivot Example

FOR appears in green not blue. Is there a different term I should be using? Right now I have Columns F1:F18 with a YES or NO as the data. I want to change that so I Have a column named Fairways that has F1:F18 as rows.

[Ratslinger]

Hello,

To start, here is a link to the HSQLDB documentation (v1.8 included with LO Base) → Hsqldb User Guide. Always best to refer to the documentation of the database used for what is available.

Many databases do not have PIVOT/UNPIVOT. This is not in HSQLDB v1.8 or MySQL or PostgreSQL or Firebird and others. I have found this in SQL Server.

You would need to go about this is some other manner.

Edit:

Have found another possibility in PostgreSQL and newer (v2.3.x + ) HSQLDB - unnest. See this post → Pivoting a query.

Edit #2:

As another option you can, with a registered DB, read the data directly in Calc and and transpose it from:

To:

For information on transform, see this post → How do I convert rows into columns (or vice versa) in Calc?

[JohnJPDX]

Thanks for the help. Ended up going with Plan B but I’ll save this for future reference.