Oracle Sql - Convert N Rows' Column Values To N Columns In 1 Row

The trick with this compared to the other questions (e.g. 'Oracle convert rows to columns') is that my column values are arbitrary strings, rather than something I can use with dec

Solution 1:

Assuming that you don't care what order the descriptions are returned in (i.e. Jeremy Smith could just as correctly have a Description1 or "Confused" and a Description2 of "Tall"), you just need to pivot on the row number. If you care about the order the descriptions are returned in, you can add an ORDER BY clause to the window function in the ROW_NUMBER analytic function

SELECT firstName, 
       lastName,
       MAX( CASEWHEN rn =1THEN description ELSENULLEND ) description1,
       MAX( CASEWHEN rn =2THEN description ELSENULLEND ) description2,
       MAX( CASEWHEN rn =3THEN description ELSENULLEND ) description3
  FROM (SELECT firstName,
               lastName,
               description,
               row_number() over (partitionby lastName, firstName) rn
          FROM descriptions
               JOIN people USING (firstName, lastName)
         WHERE age >=25)
   GROUPBY firstname, lastname

As an aside, I'm hoping that you're actually storing a birth date and computing the person's age rather than storing the age and assuming that people are updating their age every year.

Solution 2:

I have tried this option, but it says we should give order by clause inside row analytics function as shown below,

row_number() over (partitionby lastName, firstName orderby lastName, firstName) rn

It works fine for my scenario when i put order by clause.

My scenario is user details are in table A, usergroups are in table C, and association between users and usergroups in table B. One user can have multiple usergroups. I need to get results with username with multiple usergroups in a single row

**

Query:

**

SELECT username,
MAX( CASEWHEN rn =1THEN ugroup ELSENULLEND ) usergroup1,
MAX( CASEWHEN rn =2THEN ugroup ELSENULLEND ) usergroup2,
MAX( CASEWHEN rn =3THEN ugroup ELSENULLEND ) usergroup3, 
MAX( CASEWHEN rn =4THEN ugroup ELSENULLEND ) usergroup4,
MAX( CASEWHEN rn =5THEN ugroup ELSENULLEND ) usergroup5,
from (
select 
a.user_name username, 
c.name ugroup,
row_number() over (partitionby a.user_name orderby a.user_name) rn
from users a,
usergroupmembership b,
usergroups c
where a.USER_NAME in ('aegreen',
'esportspau'
)
and a.user_id= b.user_id
and b.group_id=c.group_id
)groupby uname;

**

Query Result

**

USERNAME    USERGROUP1  USERGROUP2  USERGROUP3  USERGROUP4  USERGROUP5
aegreen US_GOLF(null)  (null)  (null)  (null)
esportspau  EMEA - FSERVICE USER_ES_ES  EMEA-CR-ONLY    (null)  (null)