|
|
|
|
|
|
|
|
|
|
Re: rows to column text [message #656527 is a reply to message #656522] |
Mon, 10 October 2016 07:58 |
|
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 #656546 is a reply to message #656538] |
Mon, 10 October 2016 19:44 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|