Oracle SQL - Convert N rows' column values to N columns in 1 row

Question

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?

Justin Cave · Accepted Answer · 2015-08-18 15:13:21Z

2

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.

edited Aug 18, 2015 at 15:13

answered Feb 16, 2012 at 22:31

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeremy Over a year ago

I don't care about ordering, so I'll try that. As for your "aside", this query is a simple fabrication to avoid having to include the many details of my actual query :)

Jeremy Over a year ago

This took me a long time to figure out how to use, but it worked perfectly once I got it (I was confused by the join logic -- it wasn't explicitly necessary for this technique which is really as simple as giving each row a row number and then using the row number to identify a row). Thanks!

Brian Webster · Accepted Answer · 2012-10-11 09:28:50Z

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)

Collectives™ on Stack Overflow

Oracle SQL - Convert N rows' column values to N columns in 1 row

2 Answers 2

2 Comments

Query:

Query Result

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Query:

Query Result

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related