Ask Your Question

UNPIVOT syntax in Basex

asked 2020-01-13 21:07:32 +0100

JohnJPDX gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-01-13 22:55:14 +0100

Ratslinger gravatar image

updated 2020-01-14 05:59:20 +0100


To start, here is a link to the HSQLDB documentation (v1.8 included with LO Base) -> 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.


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:

image description


image description

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

edit flag offensive delete link more


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

JohnJPDX gravatar imageJohnJPDX ( 2020-01-14 22:01:59 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-01-13 21:07:32 +0100

Seen: 30 times

Last updated: Jan 14