Home » SQL & PL/SQL » SQL & PL/SQL » rows & columns dynamic [merged many]
rows & columns dynamic [merged many] [message #360197] Thu, 20 November 2008 00:59 Go to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Hi

I have figured out how to select Rows as columns per item as rows and stock per warehouse.
But the thing is. I have 45 warehouses starting at 301, 302 ,303 ......

How can I modify my script so that I don't have to do this 45 times. I only dit it 7 times in my scripts. Please help.

SELECT *
FROM (SELECT T$ITEM,
sum(decode(T$CWAR,301,T$STOC,0)) WAR301,
sum(decode(T$CWAR,302,T$STOC,0)) WAR302,
sum(decode(T$CWAR,303,T$STOC,0)) WAR303,
sum(decode(T$CWAR,304,T$STOC,0)) WAR304,
sum(decode(T$CWAR,305,T$STOC,0)) WAR305,
sum(decode(T$CWAR,306,T$STOC,0)) WAR306,
sum(decode(T$CWAR,307,T$STOC,0)) WAR307,
sum(decode(T$CWAR,308,T$STOC,0)) WAR308,
sum(T$STOC) TOTAL

FROM baandb.ttdinv001700
GROUP BY T$ITEM)
ORDER BY 1;
Re: Rows as Colums but alot of them. [message #360202 is a reply to message #360197] Thu, 20 November 2008 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Tue, 14 October 2008 11:09
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel



And to answer, what is the problem with copy, paste and replace features of your favorite editor?

Regards
Michel
Re: Rows as Colums but alot of them. [message #360204 is a reply to message #360197] Thu, 20 November 2008 01:18 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
We add warehouses now and then.

Thanks
Re: Rows as Colums but alot of them. [message #360208 is a reply to message #360204] Thu, 20 November 2008 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Deon Smit wrote on Thu, 20 November 2008 08:18
We add warehouses now and then.

Thanks

And?
Re: Rows as Colums but alot of them. [message #360357 is a reply to message #360197] Thu, 20 November 2008 11:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can create your select statement dynamically by selecting from the user_tab_columns data dictionary view, then execute it dynamically.
rows & columns dynamic [merged] [message #360896 is a reply to message #360197] Mon, 24 November 2008 04:58 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
My Script pulls rows as columns
SELECT T$ITEM,
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$1 ELSE 0 END) AS "2005 JAN",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$2 ELSE 0 END) AS "2005 FEB",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$3 ELSE 0 END) AS "2005 MARCH",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$1 ELSE 0 END) AS "2006 JAN",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$2 ELSE 0 END) AS "2006 FEB",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$3 ELSE 0 END) AS "2006 MARCH"
FROM baandb.ttdinv750700
GROUP BY T$ITEM

This select only few years and month. My problem is My Dates ranges from 2004 to today. And new months and years gets added. I need my script to be dynamic. T$AUPP$1 to T$AUPP$12 is my months.
Re: Rows and Columns Dynamic. [message #360903 is a reply to message #360896] Mon, 24 November 2008 05:37 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Do you run this statement from SQL*Plus? If so, use an ampersand before the variable names for sqlplus to prompt you for the values. For more info see the SQL*Plus FAQ.

Regards.

Frank
Re: Rows and Columns Dynamic. [message #360910 is a reply to message #360896] Mon, 24 November 2008 05:56 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
No I don't. I use Toad to run my Statements. Then Create a View then I use Crystal Reports for my Reporting.
Re: Rows and Columns Dynamic. [message #360911 is a reply to message #360910] Mon, 24 November 2008 06:02 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Good heavens! Check to see if this will help - http://www.orafaq.com/node/1922
Re: Rows and Columns Dynamic. [message #360916 is a reply to message #360896] Mon, 24 November 2008 06:15 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
I know how to create my views ect. I just want to know how to do my select statement. I want it to name the columns atoumaticaly if the new columns gets added to the DB.
To Shorten the script [message #360925 is a reply to message #360896] Mon, 24 November 2008 06:28 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Hi. This is my script. Is there any way that I can Shorten this.

My Range is always from 301 to 341

SELECT *
FROM (SELECT T$ITEM,
sum(decode(T$CWAR,301,T$STOC,0)) "301",
sum(decode(T$CWAR,302,T$STOC,0)) "302",
sum(decode(T$CWAR,303,T$STOC,0)) "303",
sum(decode(T$CWAR,304,T$STOC,0)) "304",
sum(decode(T$CWAR,305,T$STOC,0)) "305",
sum(decode(T$CWAR,306,T$STOC,0)) "306",
sum(decode(T$CWAR,307,T$STOC,0)) "307",
sum(decode(T$CWAR,308,T$STOC,0)) "308",
sum(decode(T$CWAR,309,T$STOC,0)) "309",
sum(decode(T$CWAR,310,T$STOC,0)) "310",
sum(decode(T$CWAR,311,T$STOC,0)) "311",
sum(decode(T$CWAR,312,T$STOC,0)) "312",
sum(decode(T$CWAR,313,T$STOC,0)) "313",
sum(decode(T$CWAR,314,T$STOC,0)) "314",
sum(decode(T$CWAR,315,T$STOC,0)) "315",
sum(decode(T$CWAR,316,T$STOC,0)) "316",
sum(decode(T$CWAR,317,T$STOC,0)) "317",
sum(decode(T$CWAR,318,T$STOC,0)) "318",
sum(decode(T$CWAR,319,T$STOC,0)) "319",
sum(decode(T$CWAR,320,T$STOC,0)) "320",
sum(decode(T$CWAR,321,T$STOC,0)) "321",
sum(decode(T$CWAR,322,T$STOC,0)) "322",
sum(decode(T$CWAR,323,T$STOC,0)) "323",
sum(decode(T$CWAR,324,T$STOC,0)) "324",
sum(decode(T$CWAR,325,T$STOC,0)) "325",
sum(decode(T$CWAR,326,T$STOC,0)) "326",
sum(decode(T$CWAR,327,T$STOC,0)) "327",
sum(decode(T$CWAR,328,T$STOC,0)) "328",
sum(decode(T$CWAR,329,T$STOC,0)) "329",
sum(decode(T$CWAR,330,T$STOC,0)) "330",
sum(decode(T$CWAR,331,T$STOC,0)) "331",
sum(decode(T$CWAR,332,T$STOC,0)) "332",
sum(decode(T$CWAR,333,T$STOC,0)) "333",
sum(decode(T$CWAR,334,T$STOC,0)) "334",
sum(decode(T$CWAR,335,T$STOC,0)) "335",
sum(decode(T$CWAR,336,T$STOC,0)) "336",
sum(decode(T$CWAR,337,T$STOC,0)) "337",
sum(decode(T$CWAR,338,T$STOC,0)) "338",
sum(decode(T$CWAR,339,T$STOC,0)) "339",
sum(decode(T$CWAR,340,T$STOC,0)) "340",
sum(decode(T$CWAR,341,T$STOC,0)) "341",
sum(T$STOC) TOTAL

FROM baandb.ttdinv001700
GROUP BY T$ITEM)
ORDER BY 1;
Re: Rows and Columns Dynamic. [message #360941 is a reply to message #360916] Mon, 24 November 2008 07:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How do you plan to use a view of which you don't know upfront how many and which columns it has?
Re: To Shorten the script [message #360943 is a reply to message #360925] Mon, 24 November 2008 07:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, but you can generate it using a macro (in an editor) or using plain sql.
Re: To Shorten the script [message #361034 is a reply to message #360925] Mon, 24 November 2008 22:44 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And in SQL , you can create the Sql string using the following query.

SELECT SQLSTR FROM (
WITH NOS as ( SELECT 299+LEVEL L FROM DUAL CONNECT BY level<=42)
SELECT L RN,'sum(decode(T$CWAR,'||L||',T$STOC,0)) "'||L||'",' sqlstr
FROM NOS 
UNION
SELECT 500, 'sum(T$STOC) TOTAL FROM baandb.ttdinv001700 GROUP BY T$ITEM)ORDER BY 1' FROM DUAL
union
SELECT 1,'SELECT * FROM (SELECT T$ITEM,' 
FROM DUAL  )
order BY RN


Ooops.. I didn't see Frank's solution of Plain SQL.

Smile
Rajuvan.

[Updated on: Mon, 24 November 2008 22:45]

Report message to a moderator

Re: To Shorten the script [message #361057 is a reply to message #360925] Mon, 24 November 2008 23:45 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
I am using Toad. Script is what I get from your Script as a result. Where should I run this?

Thank you for the reply.

Re: To Shorten the script [message #361059 is a reply to message #360925] Mon, 24 November 2008 23:48 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
SELECT *
FROM (SELECT T$ITEM,
             <some pivot columns>
      FROM baandb.ttdinv001700
      GROUP BY T$ITEM)
ORDER BY 1;

You may remove the outer SELECT and order directly in the inner one:
SELECT T$ITEM,
       <some pivot columns>
FROM baandb.ttdinv001700
GROUP BY T$ITEM
ORDER BY 1;

But I have a feeling that this is not the shortening you would expect.
Re: To Shorten the script [message #361061 is a reply to message #360925] Mon, 24 November 2008 23:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You have to execute the resultant quere where ever you need to run the actual query. Will work in TOAD also.

Incorporate the flyboy's suggestion to avoid the Outer select and order by .

Smile
Rajuvan.
Re: To Shorten the script [message #361064 is a reply to message #361059] Tue, 25 November 2008 00:02 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
THanks but it is not.

DS
Re: To Shorten the script [message #361067 is a reply to message #361064] Tue, 25 November 2008 00:05 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
I execute in TOAD. This is my result.
Sorry I am a bit new to this. What should I do in Toad.

Regards


SELECT SQLSTR FROM (
WITH NOS as ( SELECT 299+LEVEL L FROM DUAL CONNECT BY level<=42)
SELECT L RN,'sum(decode(T$CWAR,'||L||',T$STOC,0)) "'||L||'",' sqlstr
FROM NOS
UNION
SELECT 500, 'sum(T$STOC) TOTAL FROM baandb.ttdinv001700 GROUP BY T$ITEM)ORDER BY
union
SELECT 1,'SELECT * FROM (SELECT T$ITEM,'
FROM DUAL )
order BY RN
SQLSTR
--------------------------------------------------------------------------------
SELECT * FROM (SELECT T$ITEM,
sum(decode(T$CWAR,300,T$STOC,0)) "300",
sum(decode(T$CWAR,301,T$STOC,0)) "301",
sum(decode(T$CWAR,302,T$STOC,0)) "302",
sum(decode(T$CWAR,303,T$STOC,0)) "303",
sum(decode(T$CWAR,304,T$STOC,0)) "304",
sum(decode(T$CWAR,305,T$STOC,0)) "305",
sum(decode(T$CWAR,306,T$STOC,0)) "306",
sum(decode(T$CWAR,307,T$STOC,0)) "307",
sum(decode(T$CWAR,308,T$STOC,0)) "308",
sum(decode(T$CWAR,309,T$STOC,0)) "309",
sum(decode(T$CWAR,310,T$STOC,0)) "310",
sum(decode(T$CWAR,311,T$STOC,0)) "311",
sum(decode(T$CWAR,312,T$STOC,0)) "312",
sum(decode(T$CWAR,313,T$STOC,0)) "313",
sum(decode(T$CWAR,314,T$STOC,0)) "314",
sum(decode(T$CWAR,315,T$STOC,0)) "315",
sum(decode(T$CWAR,316,T$STOC,0)) "316",
sum(decode(T$CWAR,317,T$STOC,0)) "317",
sum(decode(T$CWAR,318,T$STOC,0)) "318",
sum(decode(T$CWAR,319,T$STOC,0)) "319",
sum(decode(T$CWAR,320,T$STOC,0)) "320",
sum(decode(T$CWAR,321,T$STOC,0)) "321",
sum(decode(T$CWAR,322,T$STOC,0)) "322",
sum(decode(T$CWAR,323,T$STOC,0)) "323",
sum(decode(T$CWAR,324,T$STOC,0)) "324",
sum(decode(T$CWAR,325,T$STOC,0)) "325",
sum(decode(T$CWAR,326,T$STOC,0)) "326",
sum(decode(T$CWAR,327,T$STOC,0)) "327",
sum(decode(T$CWAR,328,T$STOC,0)) "328",
sum(decode(T$CWAR,329,T$STOC,0)) "329",
sum(decode(T$CWAR,330,T$STOC,0)) "330",
sum(decode(T$CWAR,331,T$STOC,0)) "331",
sum(decode(T$CWAR,332,T$STOC,0)) "332",
sum(decode(T$CWAR,333,T$STOC,0)) "333",
sum(decode(T$CWAR,334,T$STOC,0)) "334",
sum(decode(T$CWAR,335,T$STOC,0)) "335",
sum(decode(T$CWAR,336,T$STOC,0)) "336",
sum(decode(T$CWAR,337,T$STOC,0)) "337",
sum(decode(T$CWAR,338,T$STOC,0)) "338",
sum(decode(T$CWAR,339,T$STOC,0)) "339",
sum(decode(T$CWAR,340,T$STOC,0)) "340",
sum(decode(T$CWAR,341,T$STOC,0)) "341",
sum(T$STOC) TOTAL FROM baandb.ttdinv001700 GROUP BY T$ITEM)ORDER BY 1
44 rows selected


Re: To Shorten the script [message #361072 is a reply to message #360925] Tue, 25 November 2008 00:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Copy the Query ( Make sure that this is the query you are looking for) in any editor and paste is TOAD SQL Editor and Execute it.

As simple as that .

Now I am confused (as afraid by flyboy) whether this is what OP meant by "shortening"

Smile
Rajuvan
Re: To Shorten the script [message #361083 is a reply to message #361064] Tue, 25 November 2008 00:31 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Deon Smit wrote on Tue, 25 November 2008 07:02
THanks but it is not.

Unfortunately for you, this is the only one, which is possible.
You were just told, how to generate the query instead of typing it (code for creating code). You may go further, put that code into file and execute it (do not know, how to do it in TOAD, but its help should guide you). Nothing more.
Re: To Shorten the script [message #361084 is a reply to message #361072] Tue, 25 November 2008 00:32 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
I am looking to do the folloeing on another script.

SELECT T$ITEM,
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$1 ELSE 0 END) AS "2005 JAN",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$2 ELSE 0 END) AS "2005 FEB",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$3 ELSE 0 END) AS "2005 MARCH",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$1 ELSE 0 END) AS "2006 JAN",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$2 ELSE 0 END) AS "2006 FEB",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$3 ELSE 0 END) AS "2006 MARCH"
FROM baandb.ttdinv750700
GROUP BY T$ITEM

This select only few years and month. and the forst 3 month.My problem is My Dates ranges from 2004 to today. And new months and years gets added. I need my script to be dynamic.T$AUPP$1 to T$AUPP$12 is my months.
Re: To Shorten the script [message #361093 is a reply to message #360925] Tue, 25 November 2008 00:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I hope it was Answered here.

If not Satisfied , Continue in that thread.

Please don't Cross post.

Smile
Rajuvan
Re: Rows and Columns Dynamic. [message #361098 is a reply to message #360941] Tue, 25 November 2008 00:58 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
It must always show the last 12 month data. So 12 Months and item. 13 Columns.
Re: Rows and Columns Dynamic. [message #361110 is a reply to message #361098] Tue, 25 November 2008 01:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
OK, then you must translate that logic to where you now hardcoded the 2005 and the month-names.
Re: Rows and Columns Dynamic. [message #361890 is a reply to message #361110] Fri, 28 November 2008 07:56 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
Can you guide me in the right direction for that. What functions etc. should I read up on.
Decode with Loop [message #362230 is a reply to message #360197] Mon, 01 December 2008 06:43 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
This is my Query. It decodes a table turning it into a type of pivot. It is very long. Is there a way to create a loop with this. Looping the year from 2002 to 2020. Regards


SELECT T$ITEM,
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$1 ELSE 0 END) AS "2005 JANUARY",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$2 ELSE 0 END) AS "2005 FEBUARY",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$3 ELSE 0 END) AS "2005 MARCH",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$4 ELSE 0 END) AS "2005 APRIL",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$5 ELSE 0 END) AS "2005 MAY",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$6 ELSE 0 END) AS "2005 JUNE",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$7 ELSE 0 END) AS "2005 JULY",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$8 ELSE 0 END) AS "2005 AUGUST",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$9 ELSE 0 END) AS "2005 SEPT",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$10 ELSE 0 END) AS "2005 OCTOBER",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$11 ELSE 0 END) AS "2005 NOVEMBER",
SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$12 ELSE 0 END) AS "2005 DECEMBER",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$1 ELSE 0 END) AS "2006 JANUARY",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$2 ELSE 0 END) AS "2006 FEBUARY",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$3 ELSE 0 END) AS "2006 MARCH",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$4 ELSE 0 END) AS "2006 APRIL",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$5 ELSE 0 END) AS "2006 MAY",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$6 ELSE 0 END) AS "2006 JUNE",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$7 ELSE 0 END) AS "2006 JULY",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$8 ELSE 0 END) AS "2006 AUGUST",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$9 ELSE 0 END) AS "2006 SEPT",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$10 ELSE 0 END) AS "2006 OCTOBER",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$11 ELSE 0 END) AS "2006 NOVEMBER",
SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$12 ELSE 0 END) AS "2006 DECEMBER",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$1 ELSE 0 END) AS "2007 JANUARY",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$2 ELSE 0 END) AS "2007 FEBUARY",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$3 ELSE 0 END) AS "2007 MARCH",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$4 ELSE 0 END) AS "2007 APRIL",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$5 ELSE 0 END) AS "2007 MAY",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$6 ELSE 0 END) AS "2007 JUNE",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$7 ELSE 0 END) AS "2007 JULY",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$8 ELSE 0 END) AS "2007 AUGUST",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$9 ELSE 0 END) AS "2007 SEPT",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$10 ELSE 0 END) AS "2007 OCTOBER",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$11 ELSE 0 END) AS "2007 NOVEMBER",
SUM(CASE WHEN T$YEAR=2007 THEN T$AUPP$12 ELSE 0 END) AS "2007 DECEMBER",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$1 ELSE 0 END) AS "2008 JANUARY",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$2 ELSE 0 END) AS "2008 FEBUARY",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$3 ELSE 0 END) AS "2008 MARCH",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$4 ELSE 0 END) AS "2008 APRIL",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$5 ELSE 0 END) AS "2008 MAY",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$6 ELSE 0 END) AS "2008 JUNE",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$7 ELSE 0 END) AS "2008 JULY",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$8 ELSE 0 END) AS "2008 AUGUST",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$9 ELSE 0 END) AS "2008 SEPT",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$10 ELSE 0 END) AS "2008 OCTOBER",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$11 ELSE 0 END) AS "2008 NOVEMBER",
SUM(CASE WHEN T$YEAR=2008 THEN T$AUPP$12 ELSE 0 END) AS "2008 DECEMBER",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$1 ELSE 0 END) AS "2009 JANUARY",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$2 ELSE 0 END) AS "2009 FEBUARY",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$3 ELSE 0 END) AS "2009 MARCH",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$4 ELSE 0 END) AS "2009 APRIL",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$5 ELSE 0 END) AS "2009 MAY",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$6 ELSE 0 END) AS "2009 JUNE",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$7 ELSE 0 END) AS "2009 JULY",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$8 ELSE 0 END) AS "2009 AUGUST",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$9 ELSE 0 END) AS "2009 SEPT",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$10 ELSE 0 END) AS "2009 OCTOBER",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$11 ELSE 0 END) AS "2009 NOVEMBER",
SUM(CASE WHEN T$YEAR=2009 THEN T$AUPP$12 ELSE 0 END) AS "2009 DECEMBER"
FROM baandb.ttdinv750700
GROUP BY T$ITEM
Re: Decode with Loop [message #362231 is a reply to message #362230] Mon, 01 December 2008 06:46 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still the same question, find a new one!
And still not formatted.

Regards
Michel

[Updated on: Mon, 01 December 2008 06:52]

Report message to a moderator

Previous Topic: Complex query - Time difference within duplicates
Next Topic: Getting error 'invalid column name' When filling and opening ref cursor or oracle object.
Goto Forum:
  


Current Time: Fri Dec 09 08:06:55 CST 2016

Total time taken to generate the page: 0.08107 seconds