CODEWITHSUNDEEP
sqloracle-databasepivot
Jeremy
Jeremy

Reputation: 5435

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 decode. Take this query:

The description table here maps people's names to descriptions, but each person can have multiple descriptions e.g. "wears a hat" or "is tall".

Select firstName, lastName, 
(Select description from descriptions --This can return any number of rows (0 or more)
 where description.firstName = people.firstName
 and description.lastName = people.lastName
 and rownum <= 3)
from people
where age >= 25;

I would want an output like this:

FIRSTNAME LASTNAME DESCRIPTION1 DESCRIPTION2 DESCRIPTION3
Jeremy    Smith    Tall         Confused        (null)
Anne      Smith    (Null)       (Null)          (Null)
Mark      Davis    Short        Smart           Strong

In the case of less than 3 descriptions, I want nulls there. In the case of more than 3 descriptions, I want to just leave them out.

I am using Oracle 11.1. Can this be done efficiently?

Upvotes: 0

Views: 20014

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

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( CASE WHEN rn = 1 THEN description ELSE NULL END ) description1,
       MAX( CASE WHEN rn = 2 THEN description ELSE NULL END ) description2,
       MAX( CASE WHEN rn = 3 THEN description ELSE NULL END ) description3
  FROM (SELECT firstName,
               lastName,
               description,
               row_number() over (partition by lastName, firstName) rn
          FROM descriptions
               JOIN people USING (firstName, lastName)
         WHERE age >= 25)
   GROUP BY 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.

Upvotes: 2

Naveen Venkat
Naveen Venkat

Reputation: 1

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

row_number() over (partition by lastName, firstName order by 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( CASE WHEN rn = 1 THEN ugroup ELSE NULL END ) usergroup1,
MAX( CASE WHEN rn = 2 THEN ugroup ELSE NULL END ) usergroup2,
MAX( CASE WHEN rn = 3 THEN ugroup ELSE NULL END ) usergroup3, 
MAX( CASE WHEN rn = 4 THEN ugroup ELSE NULL END ) usergroup4,
MAX( CASE WHEN rn = 5 THEN ugroup ELSE NULL END ) usergroup5,
from (
select 
a.user_name username, 
c.name ugroup,
row_number() over (partition by a.user_name order by 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
)group by 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)

Upvotes: 0

Related Questions