Hi,

I tried the SQL query (PIVOT table) in the personal environment but it didn't work. Why?

I tried running in an enterprise environment, it was OK (Oracle).

How can i run it in personal environment ?


Thanks & Best Regards.

I'm not entirely sure OUTSYSTEMS recognises the plsql PIVOT/UNPIVOT operators. 

What happens when you run your query without using PIVOT?

You must alias a sub-queries

Change:
SELECT ... FROM (...) PIVOT ...

To (example):

SELECT ... FROM (...) p PIVOT ...

And after, put de alias.[column] in your attributes.

Because the personal environment uses SQL Server and you need this changes


Sam Long wrote:

I'm not entirely sure OUTSYSTEMS recognises the plsql PIVOT/UNPIVOT operators. 

What happens when you run your query without using PIVOT?

Hi Sam Long, 

I want to get more complex data and want to display it simply on the Data Grid like a PIVOT table.
For example, get 1000 records from the DB but only display 100 rows (pivot tables) on the Data Grid.
100 row for 1000 records...


Eduardo Pires wrote:

You must alias a sub-queries

Change:
SELECT ... FROM (...) PIVOT ...

To (example):

SELECT ... FROM (...) p PIVOT ...

And after, put de alias.[column] in your attributes.

Because the personal environment uses SQL Server and you need this changes


Thanks Eduardo,

I tried but it still does not work.

Does it only work with Oracle DB?


Best Regards.

Huyen IT wrote:

Eduardo Pires wrote:

You must alias a sub-queries

Change:
SELECT ... FROM (...) PIVOT ...

To (example):

SELECT ... FROM (...) p PIVOT ...

And after, put de alias.[column] in your attributes.

Because the personal environment uses SQL Server and you need this changes


Thanks Eduardo,

I tried but it still does not work.

Does it only work with Oracle DB?


Best Regards.

PIVOT work with SQL Server, docs: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

Try alias the table inside a sub-query, like this:

select t2.* form (select t1.* from table as t1 ) as t2 pivot (...)


Hey Huyen,

I believe the SUMs in the PIVOT should not have aliases and the format of the PIVOT values is incorrect for SQL Server.

This works (see below image) for SQL Server, though I am not certain it is the same logic as your Oracle PIVOT.

I hope this helps!

Kind regards,

Stuart


Thanks Eduardo Pires,

Thanks Stuart Harris,

I tried it but it didn't pivot as many columns.


please check this post and this forge component.

Nuno Miguel Verdasca wrote:

please check this post and this forge component.

I read it but it didn't help. I want to create dynamic columns in data grid so I don't want to use Pivot Component.

Regards.

In Oracle DB


And this is what I want to get


And the final result on Data Gird, it dynamic for every year i want


So, How can i get it on SQL (Sever) query?

Regards.