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







rows & columns dynamic [merged] [message #360896 is a reply to message #360197] 
Mon, 24 November 2008 04:58 
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.







To Shorten the script [message #360925 is a reply to message #360896] 
Mon, 24 November 2008 06:28 
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: To Shorten the script [message #361034 is a reply to message #360925] 
Mon, 24 November 2008 22:44 

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.
Rajuvan.
[Updated on: Mon, 24 November 2008 22:45] Report message to a moderator




Re: To Shorten the script [message #361059 is a reply to message #360925] 
Mon, 24 November 2008 23:48 
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 #361067 is a reply to message #361064] 
Tue, 25 November 2008 00:05 
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 #361083 is a reply to message #361064] 
Tue, 25 November 2008 00:31 
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 
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.







Decode with Loop [message #362230 is a reply to message #360197] 
Mon, 01 December 2008 06:43 
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




Goto Forum:
Current Time: Sun Oct 23 11:56:14 CDT 2016
Total time taken to generate the page: 0.11731 seconds
