question

BernardBeckett avatar image
BernardBeckett asked

SQL Pivot Query

Hi,

Please could someone help me?!?

I have the following table:

QF_CoQF_BUQF_GLCodeFSCSYR

PD01SC

PD02SC

Pd03SC

PD04SC

PD05SC

PD06SC

PD01ZAR

PD02ZAR

PD03ZAR

PD04ZARPD05ZARPD06ZAR661715212020251291.311251291.311251291.311676194.587912038.6951354396.3245289.68215289.68215289.682114233.896119198.414528510.0426

And I need it to look like this:

QF_CoQF_BUQF_GLCodeFSCSYRPeriodVALUE_SCVALUE_ZAR661715212020PD01SC251291.3115289.6821661715212020PD02SC251291.3115289.682166171521
2020PD03SC251291.3115289.6821661715212020PD04SC676194.58714233.8961661715212020PD05SC912038.69519198.4145661715212020Pd06SC1354396.32428510.0426

Could someone please help me with a Pivot Query that'll produce this?

pivot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

That's hideously hard to read, sample data would help if you could give a temp table creation statement with inserts. But essentially I think you don't need to PIVOT, you just need to concatenate the data? In that case, SELECT column1+column2+column3 (in whatever order) FROM yourTable

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BernardBeckett avatar image
BernardBeckett answered

Thanks @Jon Crawford but I got assistance for this already.

WRT the format, I did put everything in a table, but this is the format that it spat out, LOL

Thanks anyway for the prompt reply!

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

What Jon is suggesting is the you "put everything in a table" FOR US! For future posts, provide the CREATE TABLE statement and an INSERT/VALUES statement to populate it with data.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

greff379 avatar image
greff379 answered

Oracle PIVOT operator allows you to cross-request a table to those who started using Oracle 11g. This means that you can combine your results and rotate rows into columns.

SYNTAX FOR THE PIVOT OPERATOR IN ORACLE/PLSQL

SELECT * FROM
(
SELECT column1_id, column2_id
FROM tabs
WHERE conds
)
PIVOT
(
aggregate_function(column2_id)
FOR column2_id
IN ( expr1_id, expr2_id, ... expr_n_id) | subquery
)
ORDER BY expression_id [ ASC | DESC ];

Parameters or arguments

  • aggregate_function – It can be a function such as SUM, COUNT, MIN, MAX or AVG.
  • IN (expr1_id, expr2_id, … expr_n_id) – List of values to rotate column2_id in the header of the cross tabular query result.
  • subquery – A subquery can be used instead of a list of values. In this case, the results of the subquery will be used to determine the values for rotating column2 in the header of the cross tabular query result.

THE PIVOT OPERATOR CAN BE USED IN FUTURE VERSIONS OF ORACLE/PLSQL

Oracle 12c, Oracle 11g

HOW TO USE THE PIVOT OFFER IN ORACLE

We’ll base our example on a table called orders with the following definition:

CREATE TABLE ords
( order_id integer NOT NULL,
custom_ref varchar2(50) NOT NULL,
order_date date,
prod_id integer,
quantity_id integer,
CONSTRAINT orders_pk_id PRIMARY KEY (order_id)
);

In order to show you the data for this example, we will select records from the order table with the following SELECT query:

SELECT order_id, custom_ref, prod_id
FROM ords
ORDER BY ord_id;

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dnirmania avatar image
Dnirmania answered

Can you provide table info and your requirement in better format. It's really hard to understand.
Additionally, you can use following articles to write your Pivot Query.

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.