SQL Query [message #343511] |
Wed, 27 August 2008 07:56  |
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   |
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 #343529 is a reply to message #343515] |
Wed, 27 August 2008 08:24   |
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   |
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 #343680 is a reply to message #343511] |
Wed, 27 August 2008 16:41   |
Frank
Messages: 7901 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   |
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"
--------------------------------------------------------------------------------
|
|
|
|
|