Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (Oracle9i - 9.2.0.7.0)
SQL Query [message #343511] Wed, 27 August 2008 07:56 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,
I have a table called EXEL_GLR_CCLINES_tmp with the following columns,
 Name                            Null?    Type
 ------------------------------- -------- ----
 SEGMENT5                                 VARCHAR2(25)
 PERIOD_NAME                              VARCHAR2(15)


Table creation and sample data creation code,
  Create table EXEL_GLR_CCLINES_tmp 
  (SEGMENT5 Varchar2(25),
  Period_Name Varchar2(15));

Insert into  EXEL_GLR_CCLINES_tmp values(1000,'AP01-08');
Insert into  EXEL_GLR_CCLINES_tmp values(1000,'AP01-08');
Insert into  EXEL_GLR_CCLINES_tmp values(1000,'AP02-08');
Insert into  EXEL_GLR_CCLINES_tmp values(1000,'AP03-08');
Insert into  EXEL_GLR_CCLINES_tmp values(1000,'AP04-08');
Insert into  EXEL_GLR_CCLINES_tmp values(1001,'AP01-08');
Insert into  EXEL_GLR_CCLINES_tmp values(1001,'AP02-08');
Insert into  EXEL_GLR_CCLINES_tmp values(1001,'AP03-08');
Insert into  EXEL_GLR_CCLINES_tmp values(1001,'AP03-08');

select period_name,count(1),segment5 from EXEL_GLR_CCLINES_Tmp
group by  period_name,segment5;

PERIOD_NAME      COUNT(1) SEGMENT5
--------------- --------- -------------------------
AP01-08                 2 1000
AP01-08                 1 1001
AP02-08                 1 1000
AP02-08                 1 1001
AP03-08                 2 1001
AP04-08                 1 1000



I want an ouput which is displayed as,

Segment5  AP01-08 AP02-08 AP03-08  AP04-080
1000        2      1        1        1
1001        1      1        2        0
and so on..


Can someone help me to achieve this?
Re: SQL Query [message #343515 is a reply to message #343511] Wed, 27 August 2008 08:04 Go to previous messageGo to next message
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
Junior Member
test@ora>
test@ora> --
test@ora> select segment5,
  2         max(decode(period_name,'AP01-08',cnt,0)) as ap01_08,
  3         max(decode(period_name,'AP02-08',cnt,0)) as ap02_08,
  4         max(decode(period_name,'AP03-08',cnt,0)) as ap03_08,
  5         max(decode(period_name,'AP04-08',cnt,0)) as ap04_08
  6  from (
  7    select segment5,
  8           period_name,
  9           count(*) as cnt
 10    from exel_glr_cclines_tmp
 11    group by segment5, period_name
 12  )
 13  group by segment5;

SEGMENT5      AP01_08    AP02_08    AP03_08    AP04_08
---------- ---------- ---------- ---------- ----------
1000                2          1          1          1
1001                1          1          2          0

2 rows selected.

test@ora>
test@ora>


tyler_durden
Re: SQL Query [message #343521 is a reply to message #343511] Wed, 27 August 2008 08:14 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,
Thanks for your quick reply. One more question, in the decode statement you are hardcoding the period_names" as AP01-08,AP02-08..can we write this query without hardcoding the values? since we'll be having 12 periods for a every year(for ex 2008 will have AP01-08,AP02-08,..AP12-08).


Re: SQL Query [message #343529 is a reply to message #343515] Wed, 27 August 2008 08:24 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

SQL> select SEGMENT5 "SEGMENTS" ,
2 count(decode(PERIOD_NAME,'AP01-08',PERIOD_NAME)) "AP01-08",
3 count(decode(PERIOD_NAME,'AP02-08',PERIOD_NAME)) "AP02-08",
4 count(decode(PERIOD_NAME,'AP03-08',PERIOD_NAME)) "AP03-08"
5 from EXEL_GLR_CCLINES_Tmp
6 group by SEGMENT5;
Re: SQL Query [message #343530 is a reply to message #343521] Wed, 27 August 2008 08:26 Go to previous messageGo to next message
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
Junior Member
Quote:

...in the decode statement you are hardcoding the period_names" as AP01-08,AP02-08..can we write this query without hardcoding the values? since we'll be having 12 periods for a every year(for ex 2008 will have AP01-08,AP02-08,..AP12-08).



I don't know of any method to do that other than using PL/SQL or the "PIVOT XML" feature (plain SQL) of Oracle 11g.

tyler_durden
Re: SQL Query [message #343542 is a reply to message #343521] Wed, 27 August 2008 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I posted many examples of this here, on AskTom, on usenet, you should be able to find them.

Regards
Michel
Re: SQL Query [message #343653 is a reply to message #343511] Wed, 27 August 2008 14:30 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,
I did find few examples on asktom but finding it difficult to understand and crack this in a sql statement. Can you please provide me the links where I can find your answers. Thanks.
Re: SQL Query [message #343656 is a reply to message #343653] Wed, 27 August 2008 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'd have to do the same thing that you can do: use the search field. Keywords: sys_connect_by_path and michel.

Regards
Michel
Re: SQL Query [message #343680 is a reply to message #343511] Wed, 27 August 2008 16:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Great subject title, by the way. Makes it stand out of the other gazillion threads with the same title.
Re: SQL Query [message #345098 is a reply to message #343680] Tue, 02 September 2008 05:25 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

Quote:


gazillion

TSII - Online Dictionary


--------------------------------------------------------------------------------


Query string:

Search type: Return DefinitionsMatch headwords exactlyMatch prefixesMatch substring occurring anywhere in a headwordMatch suffixesPOSIX 1003.2 (modern) regular expressionsOld (basic) regular expressionsMatch using SOUNDEX algorithmMatch headwords within Levenshtein distance oneMatch separate words within headwords

Database: AnyFirst matchWordNet (r) 2.0THE DEVIL'S DICTIONARY ((C)1911 Released April 15 1993)Easton's 1897 DictionaryThe Free On-line Dictionary of Computing (09 FEB 02)U.S. Gazetteer (1990)The Collaborative International Dictionary of English v.0.44Hitchcock's Names Dictionary (late 1800's)Jargon File (4.3.0, 30 APR 2001)Virtual Entity of Relevant Acronyms (Version 1.9, June 2002)The CIA World Factbook (1995)Webster's Revised Unabridged Dictionary (1913)
Server information

--------------------------------------------------------------------------------


No definitions found for "gazillion"

--------------------------------------------------------------------------------




Re: SQL Query [message #345129 is a reply to message #345098] Tue, 02 September 2008 06:45 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Top link in a Google search for Gazillion
Previous Topic: Need Query
Next Topic: Creating CLustered Primary Key in Oracle
Goto Forum:
  


Current Time: Sat Dec 03 04:01:46 CST 2016

Total time taken to generate the page: 0.14609 seconds