CODEWITHSUNDEEP
oracle-databaseplsql
David Silva
David Silva

Reputation: 2017

Multiple rows to columns

SELECT code_value FROM attributes attr WHERE attr.id = 6662 AND 
    attr.code IN ('ATTRIBUTE_CODE_1',
                  'ATTRIBUTE_CODE_2',
                  'ATTRIBUTE_CODE_3',
                  'ATTRIBUTE_CODE_4',
                  'ATTRIBUTE_CODE_5')
    ORDER BY code_value

Strength of results may vary (0-5 because some attributes can not exists):

Sample result:

CODE_VALUE
---------------
CODE_FOR_ATTR_1
CODE_FOR_ATTR_4
CODE_FOR_ATTR_5

I need to got only one row:

CODE_1             CODE_2             CODE_3             CODE_4             CODE_5
-------------------------------------------------------------------------------------
CODE_FOR_ATTR_1    CODE_FOR_ATTR_4    CODE_FOR_ATTR_5    NULL               NULL

What is best way to do this?

Upvotes: 0

Views: 47

Answers (1)

Sentinel
Sentinel

Reputation: 6449

It appears you are unconcerned which columns the various codes appear in as long as the relative order is preserved, but you want to flatten up to five rows down to one:

with dta as (
select code_value
     , row_number() over (partition by id order by code_value) rn
  from attributess attr
 where 1=1
   and attr.id = 6662
   and attr.code in ('ATTRIBUTE_CODE_1',
                     'ATTRIBUTE_CODE_2',
                     'ATTRIBUTE_CODE_3',
                     'ATTRIBUTE_CODE_4',
                     'ATTRIBUTE_CODE_5')
)
select *
  from dta
  pivot (max(code_value)
    FOR rn IN ( 1 code_1
              , 2 code_2
              , 3 code_3
              , 4 code_4
              , 5 code_5))

Upvotes: 2

Related Questions