Pivot

[generik0]

Hi

Great library!
Please tell me. Best way to make a pivot out of the query?

[ahmad-moussawi]

Thanks, could you give me please an example of what you want to achieve ?

[generik0]

Hi

PIVOT:
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

I can add the pivot query to a compiled .SQL but it would be nicer as a function.

Pivot makes rows to columns.

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

[ceastwood]

@ahmad-moussawi we should mention that if the pivoting is a business as usual scenario, that the user create a materialized view function in code as it would likely perform better; as for pivot in general, I need to look at the differences in engines but I'm guessing compiler support is necessary.

[generik0]

@ahmad-moussawi, @ceastwood

The whole point of the pivot table is that rows can become columns dynamically.

[ahmad-moussawi]

Yes, actually I did a quick research, it seems that this feature is supported only on SqlServer, and it's hard to emulate it's functionality for others compilers, so I think the best thing is to put it on the back log for now, maybe we provide provider-specific extensions later that support these kind of functionalities

[generik0]

Ok :-)

[jamescurran]

Actually, PIVOT has been part of PL/SQL since Oracle 11g (2007).

PostGres supports it thru its tablefunc/crosstab extension: https://www.postgresql.org/docs/current/tablefunc.html

While MySql doesn't support it directly, it can be emulated there. https://www.databasestar.com/mysql-pivot/
FirebirdSQL doesn't support it directly either, but it is listed (along with 862 other issues) as a "priority:major" improvement.