Re: Help with select statement (pivot table?)

From: <albundy1998_at_hotmail.com>
Date: 4 May 2005 09:06:28 -0700
Message-ID: <1115222788.105994.66250@o13g2000cwo.googlegroups.com>

ak_tiredofspam_at_yahoo.com wrote:
> use an alias for the rownum from the inline view:
>
> select year, account, rn, sum(decode(rn, 1, Period1, 2,
> Period2, 3, Period3, 4, Period4, 5, Period5, 6, Period6, 7, Period7,
8,
>
> Period8, 9, Period9, 10, Period10, 11, Period11, 12, Period12)) from
> table1, (select rownum rn from all_objects where rownum <=12) group
by
> year, account

OK. I got it to sum. Below is the actual sql being used.

SELECT GLM_FY, GLM_KEY, GLM_OBJ, sum(case when rownum between 1 and 7 then (decode( rownum, 1, GLM_MO_BUDGET01, 2, GLM_MO_BUDGET02, 3,

GLM_MO_BUDGET03, 4, GLM_MO_BUDGET04, 5, GLM_MO_BUDGET05, 6,
GLM_MO_BUDGET06, 7, GLM_MO_BUDGET07, 8, GLM_MO_BUDGET08, 9,
GLM_MO_BUDGET09, 10, GLM_MO_BUDGET10, 11, GLM_MO_BUDGET11, 12,
GLM_MO_BUDGET12)) end)

FROM OPS$BSIDBA.GLM_MO_MSTR, (select rownum from all_objects where rownum <= 12)
WHERE (GLM_FY='2005') AND (GLM_KEY='1240') AND (GLM_OBJ='7113' or GLM_OBJ='7114') AND (GLM_VERS='01')
GROUP BY GLM_FY, GLM_KEY, GLM_OBJ This sql only works if I have one GLM_OBJ (7113). If I have 2 GLM_OBJs (7113 and 7114) then I will only see totals for 7113 only. 7114 will be a null. This is because the decode only looks at the first 12 rownums of 7113. 7114 has rownums 13 to 24 which is not defined in the decode. How can I get values into 7114. I don't want to UNION two or more statements. I was thinking about looping through the records if possible. TIA... AL Received on Wed May 04 2005 - 11:06:28 CDT