OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » row to column (plsql)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

row to column [message #648883]

Sun, 06 March 2016 05:29

poppop
Messages: 3
Registered: March 2016

Junior Member

please see the below picture and help me how write my auery:

Report message to a moderator

Re: row to column [message #648885 is a reply to message #648883]

Sun, 06 March 2016 06:25

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

This is a FAQ please do search.

Report message to a moderator

Re: row to column [message #648886 is a reply to message #648883]

Sun, 06 March 2016 06:48

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Pivoting Operations

SY.

Report message to a moderator

Re: row to column [message #648888 is a reply to message #648886]

Sun, 06 March 2016 07:14

poppop
Messages: 3
Registered: March 2016

Junior Member

i solved this question with union and pivot but execution time is to long
please introduced me to try a different solution

[Updated on: Sun, 06 March 2016 07:17]

Report message to a moderator

Re: row to column [message #648889 is a reply to message #648888]

Sun, 06 March 2016 07:19

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Read Michel's reply.

SY.

Report message to a moderator

Re: row to column [message #648894 is a reply to message #648889]

Sun, 06 March 2016 23:16

poppop
Messages: 3
Registered: March 2016

Junior Member

hello i have query as below this execution time is to long. please introduced me to try a different solution .there is my query(oracle 10g)
SELECT *
FROM (WITH T AS (SELECT COR_ID, COR_COD, WEI1, DAYS, 'WEI1' WEI_TYPE
FROM (TABLE))
SELECT *
FROM T PIVOT(MAX(WEI1) FOR(DAYS) IN('01' DAY_01,
'02' DAY_02,
'03' DAY_03,
'04' DAY_04,
'05' DAY_05,
'06' DAY_06,
'07' DAY_07,
'08' DAY_08)))
UNION
FROM (WITH T AS (SELECT COR_ID, COR_COD, WEI1, DAYS, 'WEI2' WEI_TYPE
FROM (TABLE))
SELECT *
FROM T PIVOT(MAX(WEI2) FOR(DAYS) IN('01' DAY_01,
'02' DAY_02,
'03' DAY_03,
'04' DAY_04,
'05' DAY_05,
'06' DAY_06,
'07' DAY_07,
UNION
FROM (WITH T AS (SELECT COR_ID, COR_COD, WEI3, DAYS, 'WEI2' WEI_TYPE
FROM (TABLE))
SELECT *
FROM T PIVOT(MAX(WEI3) FOR(DAYS) IN('01' DAY_01,
'02' DAY_02,
'03' DAY_03,
'04' DAY_04,
'05' DAY_05,
'06' DAY_06,
'07' DAY_07,
'08' DAY_08)))
how change this query to without union query?
that result from this:

to this:

please help me

[Updated on: Sun, 06 March 2016 23:17]

Report message to a moderator

Re: row to column [message #648901 is a reply to message #648894]

Mon, 07 March 2016 00:54

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

please help me

Please help us to help you posting what we request as we request.

Report message to a moderator

Re: row to column [message #648912 is a reply to message #648894]

Mon, 07 March 2016 06:58

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

poppop wrote on Mon, 07 March 2016 00:16

how change this query to without union query?

Did you read documentation (link I posted)? Especially Pivoting on Multiple Columns.

SY.

Report message to a moderator

Re: row to column [message #648933 is a reply to message #648912]

Mon, 07 March 2016 16:21

Bill B
Messages: 1971
Registered: December 2004

Senior Member

This is NOT an example of using the PIVOT command in Oracle. What version of the database are you using. Even if you do not have the PIVOT command available, you can easily do what you want using a single query. You did it the most expensive way you could. Once you give your database version, we can help you more.

Report message to a moderator

Previous Topic:	Issue with Sys_context
Next Topic:	Oracle SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 15:14:01 CDT 2024