SQLeoPivot

[Anonymous]

Hello, I am missing something here, I tried your software to create a complex query in my DB .. I need to pivot some data and what I see in the syntax is the software uses this function SQLeoPivot ... ?? What is this? It doesn't work in mysql. Where is the raw SQL query generated by the program? That's what I need.

Thanks,

[PAscal]

Hello,
It should work with MySQL if you use a version that support CASE ... function.

Please start SQLeo with a command line like
java - jar SQLeo.jar

then build your Pivot command using query builder or directly

SELECT
sakila.rental.inventory_id,
SQLeoPivot(sakila.rental,staff_id,count,sakila.rental.customer_id)
FROM
sakila.rental
GROUP BY
sakila.rental.inventory_id

this generates a query given in console
PivotSQL params: sakila.rental / staff_id / count / sakila.rental.customer_id
PivotSQL text: count(case staff_id when '1' then sakila.rental.customer_id else null end)
as "1",count(case staff_id when '2' then sakila.rental.customer_id else null end) as "2"
PivotSQL length: 163

If you replace SQLeoPivot by this CASE query it gives
SELECT
sakila.rental.inventory_id,
count(case staff_id when '1' then sakila.rental.customer_id else null end) as "1",count(case staff_id when '2' then sakila.rental.customer_id else null end) as "2"
FROM
sakila.rental
GROUP BY
sakila.rental.inventory_id

I Hope it helps.
Regards
PAscal

[Anonymous]

Ok but how do I export the generated query to file or text so that I can use it?

[PAscal]

added Ticket #365