Home » SQL & PL/SQL » SQL & PL/SQL » Crosstab query help needed (Oracle 9i)
Crosstab query help needed [message #326770] Thu, 12 June 2008 09:41 Go to next message
sasrs991984
Messages: 3
Registered: June 2008
Junior Member
I would like some help modifying this query:
SELECT a.ID
     , a.LASTMODIFIED
	 , d.DESCRIPTION lang_descr
	 , e.DESCRIPTION copy_type
	 , b.COPYTEXT 
FROM PRODUCT_PP a, COPY_PP b, PRODUCTCOPY_PP c, LANGUAGE_PP d, COPYTAB_PP e
WHERE a.PRODUCTID  = c.PRODUCTID
  AND c.COPYID     = b.COPYID
  AND b.LANGUAGEID = d.LANGUAGEID
  AND b.COPYTABID  = e.COPYTABID
  AND a.ID LIKE '%00004011'
ORDER BY a.ID, d.DESCRIPTION, e.ORDERID 

So that instead of returning rows like this...
ID                 LASTMODIFIED           LANG_DESCR    COPY_TYPE        COPYTEXT 
000000000004011    6/3/2008 3:09:53 PM    AC English    Brand            Cucumber 
000000000004011    6/3/2008 3:09:53 PM    AC English    Header           YELLOW  
000000000004011    6/3/2008 3:09:53 PM    AC English    Body             1EA      
000000000004011    6/3/2008 3:09:53 PM    English       Brand            Fresh    
000000000004011    6/3/2008 3:09:53 PM    English       Header           Bananas  
000000000004011    6/3/2008 3:09:53 PM    English       Body             Golden Ripe, Full of Potassium


It will compress them into single rows as such:
ID                 LASTMODIFIED           LANG_DESCR    BRAND       HEADER       BODY
000000000004011    6/3/2008 3:09:53 PM    AC English    Cucumber    YELLOW       1EA
000000000004011    6/3/2008 3:09:53 PM    English       Fresh       Bananas      Golden Ripe, Full of Potassium     



[Mod-edit: Frank added code tags]

[Updated on: Thu, 12 June 2008 11:11] by Moderator

Report message to a moderator

Re: Crosstab query help needed [message #326780 is a reply to message #326770] Thu, 12 June 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the "compress" rule is?

Also post a test case and Oracle version with 4 decimals.

Regards
Michel
Re: Crosstab query help needed [message #326783 is a reply to message #326770] Thu, 12 June 2008 10:26 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search for pivot / rows to columns in this forum, google, asktom. I am going to vote for this question as the most popular question of this century.

Regards

Raj
Re: Crosstab query help needed [message #326799 is a reply to message #326783] Thu, 12 June 2008 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have another one, "insufficient privilege" or "table or view does not exist" in PL/SQL... Wink

Regards
Michel
Re: Crosstab query help needed [message #327077 is a reply to message #326770] Fri, 13 June 2008 12:16 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you are interested in data pivoting in sql, try these:

Kevin Meade's OraFAQ Blog

Example of Data Pivots in SQL (rows to columns and columns to rows)

Good Luck, Kevin
Previous Topic: Help in Row to Col
Next Topic: Complex Query
Goto Forum:
  


Current Time: Fri Dec 09 13:30:46 CST 2016

Total time taken to generate the page: 0.09650 seconds