0

I have an SQLite table of stock orders with the following columns:

  • symbol
  • date
  • order_type ("Buy", "Sell", or "Hold")
  • price
  • amount

What I would like to be able to is return a table with symbols running down the vertical axis, dates running across the horizontal, and the order description in each cell. I can generate the order description easily enough (just SELECT order_type || " " || amount || " @ " || price), but I am having an impossible time figuring out how to format the table in this human readable form. Is this even possible with SQLite?

Plastech
  • 757
  • 6
  • 17
  • It looks like you want to group by `symbol` column and just display concatenated dates and order description. Have you tried using the `group by` clause? – pgngp Jan 13 '18 at 20:05
  • The SQL dialect used by SQLite does not support PIVOT or stored procedures so what you describe is not possible using native SQL alone. If you [edit] your question to indicate the context in which you are using SQLite (e.g. from Python, Java, C#, ...) then we might be able to show you how to dynamically build the required query. – Gord Thompson Jan 13 '18 at 22:18

1 Answers1

0

You can find many discussions on this topic under the [pivot] tag in stackoverflow. My own opinion is that anything but trivial formatting (including almost all pivot tables) is best done in the application. Use SQL to get the data (grouping and aggregating if necessary), then use a visualisation tool, or your application, to format it. I have written more about this on my website, where I explain a couple of common approaches and why I don't use them.

Ron Ballard
  • 693
  • 6
  • 8