OraFAQ Forum: SQL & PL/SQL » Transpose dynamic Rows to Columns

Home » SQL & PL/SQL » SQL & PL/SQL » Transpose dynamic Rows to Columns (Oracle 11g R2)

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

Transpose dynamic Rows to Columns [message #590125]

Mon, 15 July 2013 01:16

vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey

Member

I have a result table from a select query. Create_table.txt

FD	AVAILABILITY_2G	AVAILABILITY_3G	TCH_TRAFFIC	HOSR	CSSR
2013 Jun	0.987	0.9929	28359.0943	0.9836	0.975
2013 W27	0.9921	0.9943	28512.4865	0.9832	0.9756
2013 W28	0.9932	0.9937	28659.3256	0.9843	0.9734
2013 W29	0.9953	0.9933	28355.0263	0.9848	0.9741

I need to transpose this table to below one. But since my rows in 1 table is dynamic and it will increment each week and month I couldn't get a correct result from unpivot.

	        2013 Jun	2013 W27	2013 W28	2013 W29
AVAILABILITY_2G	0.99	        0.99	        0.99	        1.00
AVAILABILITY_3G	0.99	        0.99	        0.99	        0.99
TCH_TRAFFIC	28359.0943	28512.4865	28659.3256	28355.0263
HOSR	        0.9836	        0.9832	        0.9843	        0.9848
CSSR	        0.975	        0.9756	        0.9734	        0.9741

Is it possible to create above table from attached , with a select statement?
Thanks in advance.

Attachment: Create_table.txt
(Size: 1.36KB, Downloaded 1553 times)

[Updated on: Mon, 15 July 2013 01:22]

Report message to a moderator

Re: Transpose dynamic Rows to Columns [message #590127 is a reply to message #590125]

Mon, 15 July 2013 01:31

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You cannot unless you return an XML output (use XML option in PIVOT clause).

Regards
Michel

[Updated on: Mon, 15 July 2013 01:32]

Report message to a moderator

Re: Transpose dynamic Rows to Columns [message #590129 is a reply to message #590127]

Mon, 15 July 2013 01:39

vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey

Member

I need to write the result to an excel file and send it every week by a scheduler which is written by me.
And my scheduler and I don't know how to deal with xml. Is there any other way?

Report message to a moderator

Re: Transpose dynamic Rows to Columns [message #590132 is a reply to message #590129]

Mon, 15 July 2013 02:10

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No other way in pure SQL; standard SQL needs a static number of columns.
You can do it in PL/SQL using a pipelined function.

You have to post a test case, inline, in text, formatted, without any schema name nor tablespace... we have not the same than you.

Regards
Michel

Report message to a moderator

Re: Transpose dynamic Rows to Columns [message #590134 is a reply to message #590132]

Mon, 15 July 2013 02:24

vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey

Member

Dear Michel ,

I removed tablespace names from create_table.txt

CREATE TABLE TEST_TABLE2
(
  FD               VARCHAR2(17 BYTE),
  AVAILABILITY_2G  NUMBER,
  AVAILABILITY_3G  NUMBER,
  TCH_TRAFFIC      NUMBER,
  HOSR             NUMBER,
  CSSR             NUMBER
)

SET DEFINE OFF;
Insert into TEST_TABLE2
   (FD, AVAILABILITY_2G, AVAILABILITY_3G, TCH_TRAFFIC, HOSR, 
    CSSR)
 Values
   ('2013 Jun', 0.987, 0.9929, 28359.0943, 0.9836, 
    0.975);
Insert into TEST_TABLE2
   (FD, AVAILABILITY_2G, AVAILABILITY_3G, TCH_TRAFFIC, HOSR, 
    CSSR)
 Values
   ('2013 W27', 0.9921, 0.9943, 28512.4865, 0.9832, 
    0.9756);
Insert into TEST_TABLE2
   (FD, AVAILABILITY_2G, AVAILABILITY_3G, TCH_TRAFFIC, HOSR, 
    CSSR)
 Values
   ('2013 W28', 0.9932, 0.9937, 28659.3256, 0.9843, 
    0.9734);
Insert into TEST_TABLE2
   (FD, AVAILABILITY_2G, AVAILABILITY_3G, TCH_TRAFFIC, HOSR, 
    CSSR)
 Values
   ('2013 W29', 0.9953, 0.9933, 28355.0263, 0.9848, 
    0.9741);
COMMIT;

and below I have the query which is close to my result. If I can achieve to write each column name in 1 row in ordered it will also be enough.Actually I don't need to display Week number as column header col1, col2 will be enough.

SELECT COL_NAME, COL_VALUE
FROM 
(
SELECT *
FROM TEST_TABLE2
)
UNPIVOT
(
  COL_VALUE
  FOR COL_NAME IN (AVAILABILITY_2G, AVAILABILITY_3G, TCH_TRAFFIC, HOSR,CSSR)
) UNP

Report message to a moderator

Re: Transpose dynamic Rows to Columns [message #590143 is a reply to message #590134]

Mon, 15 July 2013 03:05

vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey

Member

Below what I am trying then I can split it to columns but It is giving the values in wrong order which means wrong result. Unpivot is a wrong desicion it gives me the result in wrong order.
I think I am going more far from correct answer. I will be appreciate to any idea.

SELECT
   COL_NAME,
   LISTAGG (COL_VALUE, ',') 
WITHIN GROUP 
(ORDER BY COL_VALUE) COL_VALUE
FROM 
(
    SELECT COL_NAME, COL_VALUE
    FROM 
    (
    SELECT *
    FROM TEST_TABLE2
    )
    UNPIVOT
    (
      COL_VALUE
      FOR COL_NAME IN (AVAILABILITY_2G, AVAILABILITY_3G, TCH_TRAFFIC, HOSR, CSSR)
    ) UNP
)
GROUP BY COL_NAME

[Updated on: Mon, 15 July 2013 03:08]

Report message to a moderator

Re: Transpose dynamic Rows to Columns [message #590145 is a reply to message #590143]

Mon, 15 July 2013 03:26

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This one (from your query with changes in lower case)?

SQL> SELECT
  2     COL_NAME,
  3     LISTAGG (COL_VALUE, ',')
  4  WITHIN GROUP
  5  (ORDER BY fd) COL_VALUE
  6  FROM
  7  (
  8      SELECT COL_NAME, COL_VALUE, fd
  9      FROM
 10      (
 11      SELECT *
 12      FROM TEST_TABLE2
 13      )
 14      UNPIVOT
 15      (
 16        COL_VALUE
 17        FOR COL_NAME IN (AVAILABILITY_2G, AVAILABILITY_3G, TCH_TRAFFIC, HOSR, CSSR)
 18      ) UNP
 19  )
 20  GROUP BY COL_NAME
 21  /
COL_NAME        COL_VALUE
--------------- --------------------------------------------------
AVAILABILITY_2G .987,.9921,.9932,.9953
AVAILABILITY_3G .9929,.9943,.9937,.9933
CSSR            .975,.9756,.9734,.9741
HOSR            .9836,.9832,.9843,.9848
TCH_TRAFFIC     28359.0943,28512.4865,28659.3256,28355.0263

Regards
Michel

Report message to a moderator

Re: Transpose dynamic Rows to Columns [message #590146 is a reply to message #590145]

Mon, 15 July 2013 04:21

vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey

Member

Thanks Michel ,

I solved problem with your help. I split ted commas to columns, now everything is fine.

Report message to a moderator

Previous Topic:	Oracle login problem
Next Topic:	Temp Table within an Stored Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 23 18:01:18 CDT 2025