Home » SQL & PL/SQL » SQL & PL/SQL » Presenting rows as columns
Presenting rows as columns [message #228875] Wed, 04 April 2007 08:12 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all..

I have a query that returns data in the form:

PROJECT_ID	PERIOD_NAME	SUM(NBI.ETC_QTY)
5008803	MAY07	306.424849
5008803	JUN07	197.390394
5008803	JUL07	170.500117
5008803	AUG07	101.159585
5008803	APR07	589.774833

excuse the formatting!

this is using the following SQL:
select  nbi.PROJECT_ID, biz.PERIOD_NAME, sum(nbi.ETC_QTY)
from 
NIKU.nbi_prt_facts nbi,
NIKU.srm_resources r,
niku.biz_com_periods biz
where 
nbi.project_id = 5008803
and nbi.RESOURCE_ID = r.ID
and nbi.fact_date >= biz.START_DATE and nbi.fact_date <= biz.END_DATE
group by  nbi.PROJECT_ID, biz.PERIOD_NAME
order by 1, 2 desc


I would like the results to be presented like:
ProjectID  Month1 Month2 ..... Monthx  
5008803    sum(ETC) sum(ETC)..... sum(ETC)


does that make sense?
I will amend the query, so that it only shows the current period_name and future periods.
The problem is some projects may have entries up to 12 months in advance, some might only have 3 months.

I need to sort all periods and assign each as columns.

so if I had the following results in my normal query:

PROJECT_ID	PERIOD_NAME	SUM(NBI.ETC_QTY)
5008803	MAY07	306.424849
5008803	JUN07	197.390394
5008803	JUL07	170.500117
5008803	AUG07	101.159585
5008803	APR07	589.774833
5008810	AUG07	306.424849
5008810	SEP07	197.390394
5008810	OCT07	170.500117
5008810	NOV07	101.159585
5008810	DEC07	589.774833


I would then see:
Project  APR07 MAY07 JUN07 JUL07 AUG07 SEP07 OCT07 NOV07 DEC07
5008803  589.8 306.4 197.4 170.5 101.2 0     0     0     0
5008810  0     0     0     0     306.4 197.4 170.5 101.2 589.8


does this make sense?
If I was to run the SQL when the current date is within the MAY07 period , then the above results would miss off APR07, and go up as far as the latest period that has time against it.

any help you can offer would be appreciated, as I have no idea where/how to start.

thanks in advance,
Matt






Re: Presenting rows as columns [message #228918 is a reply to message #228875] Wed, 04 April 2007 09:20 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You should do a search on this site or Google for 'pivot query'.
Re: Presenting rows as columns [message #228960 is a reply to message #228875] Wed, 04 April 2007 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t order by 1, 2;
PROJECT_ID PERIO        QTY
---------- ----- ----------
   5008803 APR07 589.774833
   5008803 MAY07 306.424849
   5008803 JUN07 197.390394
   5008803 JUL07 170.500117
   5008803 AUG07 101.159585
   5008810 AUG07 306.424849
   5008810 SEP07 197.390394
   5008810 OCT07 170.500117
   5008810 NOV07 101.159585
   5008810 DEC07 589.774833

10 rows selected.

SQL> set head off
SQL> col nop noprint
SQL> col prj format a07
SQL> col lin format a65
SQL> with
  2    months as ( select distinct period_name from t ),
  3    cnt as ( select count(*) cnt, min(period_name) min_period from months ),
  4    data as ( 
  5      select t.project_id, m.period_name, nvl(t.qty,0) qty,
  6             row_number() over (partition by t.project_id order by m.period_name) rn,
  7             count(*) over (partition by t.project_id) cnt
  8      from t partition by (project_id)
  9           right outer join months m 
 10           on (m.period_name = t.period_name)
 11    )
 12  select 1 nop, 'Project' prj, 
 13         substr(sys_connect_by_path(period_name,' '),2) lin
 14  from months, cnt
 15  where level = cnt
 16  connect by prior period_name = add_months(period_name,-1)
 17  start with period_name = min_period
 18  union all
 19  select 2, to_char(project_id),
 20         replace(substr(sys_connect_by_path(substr(to_char(round(qty,1),'990.0'),2),'/'),2),'/',' ') lin
 21  from data
 22  where rn = cnt
 23  connect by prior project_id = project_id and prior rn = rn-1
 24  start with rn = 1
 25  order by 1, 2
 26  /
Project APR07 MAY07 JUN07 JUL07 AUG07 SEP07 OCT07 NOV07 DEC07
5008803 589.8 306.4 197.4 170.5 101.2   0.0   0.0   0.0   0.0
5008810   0.0   0.0   0.0   0.0 306.4 197.4 170.5 101.2 589.8

3 rows selected.

Regards
Michel
Re: Presenting rows as columns [message #229073 is a reply to message #228960] Thu, 05 April 2007 04:49 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi Michel,

the end result of your code is just what I need.
the only thing is...


I seem to be having a problem with the code that defines the 'data' table:

select t.project_id, m.period_name, nvl(t.qty,0) qty,
  row_number() over (partition by t.project_id order by m.period_name) rn,
  count(*) over (partition by t.project_id) cnt
  from niku.t partition by (project_id)
  right outer join niku.months m 
 on (m.period_name = t.period_name)


I created a view named t from my original SQL, and a view named months (based on your months table definition previously), to test this bit of code out.
I get an error saying:
ORA-00933: SQL command not properly ended

and it highlights the 'by' in this lie:
from niku.t partition by (project_id)


can you advise.

I have never used 'with' before in SQL, so I opted to create those tables as view while I familiarise myself with the process.

thanks in advance,
Matt




Re: Presenting rows as columns [message #229076 is a reply to message #229073] Thu, 05 April 2007 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"partition by ()" in "from" clause is a 10g feature. Do you have 10g?

"With" is named factoring clause, it is the same as "inline view" in "from" clause but it is easier to read (I think) and avoid to repeat the same inline view in different subqueries.

Regards
Michel
Re: Presenting rows as columns [message #229078 is a reply to message #228875] Thu, 05 April 2007 05:02 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

we are using version 9 - (which is specified for our Clarity system)

is there an alternative function for this older version?


Re: Presenting rows as columns [message #229090 is a reply to message #229078] Thu, 05 April 2007 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to create one more subquery:
months_prj as (
  select t.project_id, m.period_name
  from ( select distinct project_id from t ) t,
       months m
  )

and outer join this one with t in "data":
from t right outer join months_prj m
on (m.project_id = t.project_id and m.period_name = t.period_name)

Regards
Michel
Re: Presenting rows as columns [message #229097 is a reply to message #229078] Thu, 05 April 2007 06:16 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Partition by clause for analytical function where avaialable from Oracle 8i onwards.

However they must be included in select condition and you have included in from condition and hence in error


from niku.t partition by (project_id)
right outer join niku.months m




Re: Presenting rows as columns [message #229101 is a reply to message #229097] Thu, 05 April 2007 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"partition by" in from clause (not in analytic function) is only in 10g.
The query is perfectly valid and works in 10g as I showed it (just read the execution I posted).

Regards
Michel
Re: Presenting rows as columns [message #229148 is a reply to message #229101] Thu, 05 April 2007 09:37 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
apologies... read it too fast
Re: Presenting rows as columns [message #229160 is a reply to message #229148] Thu, 05 April 2007 10:38 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't mind, it happens to me all the time.
Regards
Michel
Previous Topic: Schema Selective Copy
Next Topic: calculate breakfast,lunch and dinner time between 2 times
Goto Forum:
  


Current Time: Fri Dec 02 18:48:42 CST 2016

Total time taken to generate the page: 0.33399 seconds