Skip to Content (Press Enter)
2020-10-19 05-14-26
Huyen IT
319Views
11Comments
SQL query PIVOT table not working
Question

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.

0
0
28 Feb 2020
Copy Link
2022-01-10 17-40-49
Sam Long

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

What happens when you run your query without using PIVOT?

0
0
28 Feb 2020
Copy Link
2020-10-19 05-14-26
Huyen IT

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...


0
0
29 Feb 2020
Copy Link
2024-10-25 08-47-19
Eduardo Pires

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


1
0
28 Feb 2020
Copy Link
2020-10-19 05-14-26
Huyen IT

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.

0
0
29 Feb 2020
Copy Link
2024-10-25 08-47-19
Eduardo Pires

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 (...)


1
0
02 Mar 2020
Copy Link
2024-11-07 03-28-42
Stuart Harris
Champion

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


1
0
02 Mar 2020
Copy Link
2020-10-19 05-14-26
Huyen IT

Thanks Eduardo Pires,

Thanks Stuart Harris,

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


0
0
04 Mar 2020
Copy Link
2025-11-19 06-14-01
Miguel Verdasca
Champion

please check this post and this forge component.

0
0
04 Mar 2020
Copy Link
2020-10-19 05-14-26
Huyen IT

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.

0
0
05 Mar 2020
Copy Link
2020-10-19 05-14-26
Huyen IT

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.

0
0
05 Mar 2020
Copy Link
UserImage.jpg
Lovish Goyal

Hi Huyen,

Please check the below forge component :

https://www.outsystems.com/forge/component-overview/9800/pivot-sql-sample

I tried with simple example user role table and it is working for me 


0
0
12 Jun 2021
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.