Home » SQL & PL/SQL » SQL & PL/SQL » Help with linking tables and grouping values into columns
Help with linking tables and grouping values into columns [message #218685] Fri, 09 February 2007 05:21 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member
Hi all,

I have a table, NBI_PRT_FACTS in the following format

Project_ID Fact_Date ETC_QTY PERIOD_NAME
5000001 15/02/2007 5 FEB07
5000001 16/02/2007 5 FEB07
5000001 12/03/2007 5 MAR07
5000001 20/04/2007 5 APR07

5000002 15/02/2007 5 FEB07
5000002 16/02/2007 5 FEB07
5000002 17/02/2007 5 MAR07
5000002 18/02/2007 5 MAR07
5000002 02/03/2007 5 MAR07
5000002 03/03/2007 5 MAR07
5000002 04/03/2007 5 MAR07


I need to be able to sum up the ETC_QTY for each project by PERIOD_NAME in column format.
the results above would become:
PROJECT_ID FEB07 MAR07 APR07
5000001 10 5 5
5000002 10 25 0

can someone please help me with this, as I cant think how to do it.
Thanks in advance,
Matt






Re: Help with linking tables and grouping values into columns [message #218688 is a reply to message #218685] Fri, 09 February 2007 05:34 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


SELECT PROJECT_ID
,SUM(DECODE(PERIOD_NAME,'FEB07',ETC_QTY))FEB07
,SUM(DECODE(PERIOD_NAME,'MAR07',ETC_QTY))MAR07
,SUM(DECODE(PERIOD_NAME,'APR07',ETC_QTY))APR07
FROM TEST
GROUP BY PROJECT_ID 



regards,
Re: Help with linking tables and grouping values into columns [message #218695 is a reply to message #218688] Fri, 09 February 2007 06:11 Go to previous message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member
thanks for thats brilliant.

although, is it possible to have the query automatically name the columns?
I want to be able to run this query at any time, and it will pick up the period names in the list, and turn them to columns - rather than me having to title each column manually in the SQL...

does that make sense?
I would never be more than 12 months in advance.

thanks,
Matt
Previous Topic: INSERT: inserting huge data using '&' in Oracle 9i
Next Topic: NO ENTRY : No entry to be made in a cell, but SQL is not accepting it....What to do...!!?
Goto Forum:
  


Current Time: Sat Feb 08 06:09:00 CST 2025