Home » SQL & PL/SQL » SQL & PL/SQL » rows to column text
rows to column text [message #656477] Fri, 07 October 2016 09:36 Go to next message
bchowdam
Messages: 5
Registered: October 2016
Junior Member
/forum/fa/13280/0/SELECT *
FROM pim_navigation_xml_stg_temp t pivot ( min[attvalue] FOR([attname]) IN([contentcategoryindicator],[seotitletagtext]) )AS p;

  • Attachment: Capture.PNG
    (Size: 24.71KB, Downloaded 319 times)
Re: rows to column text [message #656478 is a reply to message #656477] Fri, 07 October 2016 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: rows to column text [message #656479 is a reply to message #656478] Fri, 07 October 2016 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/PIVOT
Re: rows to column text [message #656480 is a reply to message #656479] Fri, 07 October 2016 09:57 Go to previous messageGo to next message
bchowdam
Messages: 5
Registered: October 2016
Junior Member
Hi BlackSwan,

before posting question I followed the standards.

I tried multiple PIVOT messages in forum. Since my aggregation column is text and doesn't have more than one value. query is getting failed.

Re: rows to column text [message #656482 is a reply to message #656480] Fri, 07 October 2016 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: rows to column text [message #656496 is a reply to message #656477] Fri, 07 October 2016 18:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8904
Registered: November 2002
Location: California, USA
Senior Member
SELECT *
FROM   pim_navigation_xml_stg_temp
PIVOT  (MIN (attvalue) FOR (attname) IN
         ('ContentCategoryIndicator', 
          'SEOTitleTagText', 
          'CategoryViewCode',
          'ShowModeImageIndicator',
          'EMSAfterIndicator',
          'PriceFlagBlockDisableIndicator',
          'CategoryREadinessCode',
          'LinkedCatalogID'));
Re: rows to column text [message #656516 is a reply to message #656496] Sun, 09 October 2016 15:45 Go to previous messageGo to next message
bchowdam
Messages: 5
Registered: October 2016
Junior Member
Sorry for delayed response.

That did not work, I am using case statement. That is working fine.
Re: rows to column text [message #656520 is a reply to message #656516] Mon, 10 October 2016 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So post your solution.

Re: rows to column text [message #656521 is a reply to message #656477] Mon, 10 October 2016 00:42 Go to previous messageGo to next message
jgjeetu
Messages: 363
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

you can use chr(10) also if your oracle version supports.
Re: rows to column text [message #656522 is a reply to message #656521] Mon, 10 October 2016 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Question

Re: rows to column text [message #656527 is a reply to message #656522] Mon, 10 October 2016 07:58 Go to previous messageGo to next message
bchowdam
Messages: 5
Registered: October 2016
Junior Member
SELECT categoryid
MAX(CASE WHEN attname='SEOTitleTagTextOverrideIndicator' THEN attvalue ELSE '' END) as SEOTitleTagTextOverrideInd
,MAX(CASE WHEN attname='SearchExclusionIndicator' THEN attvalue ELSE '' END) as SearchExclusionIndicator
,MAX(CASE WHEN attname='SequenceByInventoryIndicator' THEN attvalue ELSE '' END) as SequenceByInventoryIndicator
,MAX(CASE WHEN attname='ShowModelImageIndicator' THEN attvalue ELSE '' END) as ShowModelImageIndicator
,MAX(CASE WHEN attname='ShowTopPerformingColorIndicator' THEN attvalue ELSE '' END) as ShowTopPerformingColorInd
,MAX(CASE WHEN attname='SortByRatingIndicator' THEN attvalue ELSE '' END) as SortByRatingIndicator
,MAX(CASE WHEN attname='SortEnabledIndicator' THEN attvalue ELSE '' END) as SortEnabledIndicator
,MAX(CASE WHEN attname='SortMethodCode' THEN attvalue ELSE '' END) as SortMethodCode
,MAX(CASE WHEN attname='SortingRuleTestIndicator' THEN attvalue ELSE '' END) as SortingRuleTestIndicator
,MAX(CASE WHEN attname='ViewAllChildDepartmentsIndicator' THEN attvalue ELSE '' END) as ViewAllChildDepartmentsInd
FROM PIM_NAVIGATION_XML_STG_TEMP
GROUP BY categoryid
order by categoryid
Re: rows to column text [message #656537 is a reply to message #656527] Mon, 10 October 2016 15:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8904
Registered: November 2002
Location: California, USA
Senior Member
You must be using an older outdated version of Oracle, which is why we ask for your version. What I posted requires a current version of Oracle.
Re: rows to column text [message #656538 is a reply to message #656537] Mon, 10 October 2016 15:41 Go to previous messageGo to next message
bchowdam
Messages: 5
Registered: October 2016
Junior Member
Our Oracle version 11.2.0.3.0
Re: rows to column text [message #656546 is a reply to message #656538] Mon, 10 October 2016 19:44 Go to previous message
Barbara Boehmer
Messages: 8904
Registered: November 2002
Location: California, USA
Senior Member
Pivot was available in 11g. I have provided my version and test below. There are two possibilities here. Either you are using something other than SQL*Plus to access your Oracle database, which is limiting which version features are available or the sample data that you displayed was not sufficient to demonstrate your needs. I only entered enough of that data, since you did not provide code to create table or insert, to test the code. For example, if there is more than the 1 id that you showed, then that might affect things.

SCOTT@orcl_12.1.0.2.0> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

5 rows selected.

SCOTT@orcl_12.1.0.2.0> create table pim_navigation_xml_stg_temp
  2    (id	  number,
  3  	attname   varchar2(24),
  4  	attvalue  varchar2(24))
  5  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into pim_navigation_xml_stg_temp values (101, 'ContentCategoryIndicator', 'No')
  3  into pim_navigation_xml_stg_temp values (101, 'SEOTitleTagText', 'boys Kids Pos |')
  4  into pim_navigation_xml_stg_temp values (101, 'CategoryViewCode', 'ViewAll')
  5  select * from dual
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> SELECT *
  2  FROM   pim_navigation_xml_stg_temp
  3  PIVOT  (MIN (attvalue) FOR (attname) IN
  4  	      ('ContentCategoryIndicator',
  5  	       'SEOTitleTagText',
  6  	       'CategoryViewCode',
  7  	       'ShowModeImageIndicator',
  8  	       'EMSAfterIndicator',
  9  	       'PriceFlagBlockDisableIndicator',
 10  	       'CategoryREadinessCode',
 11  	       'LinkedCatalogID'));

        ID 'ContentCategoryIndicato 'SEOTitleTagText'        'CategoryViewCode'       'ShowModeImageIndicator'
---------- ------------------------ ------------------------ ------------------------ ------------------------
'EMSAfterIndicator'      'PriceFlagBlockDisableIn 'CategoryREadinessCode'  'LinkedCatalogID'
------------------------ ------------------------ ------------------------ ------------------------
       101 No                       boys Kids Pos |          ViewAll



1 row selected.

Previous Topic: compile invalid view
Next Topic: What is Corrected By RowID? I wish to know Missing Numbers in a column
Goto Forum:
  


Current Time: Fri Nov 16 18:07:01 CST 2018