CODEWITHSUNDEEP
sqloracle-databaseaggregategrouping
welshie
welshie

Reputation: 11

Grouping multiple lines onto one in Oracle SQL

How can I get this data set in Image 1 to look like the data in Image 2. Basically rather than having each purchase on its own line I want to group by Name and have all that persons purchases on one line. They can buy a max of 5 items and my database is about 30 million lines worth of purchases.

P.S The date order is not important

Image 1

Image 2

Upvotes: 1

Views: 357

Answers (2)

Popeye
Popeye

Reputation: 35930

You can use PIVOT with row_number as follows:

Select * from 
   (select t.*,
           row_number() over (partition by name order by date_purchased) rn
      from your_table t
  ) PIVOT
(Max(item_purchased), max(date_purchased) For rn in (1,2,3));

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can use row_number() and conditional aggregation:

select name,
       max(case when seqnum = 1 then item end) as item_1,
       max(case when seqnum = 1 then date end) as date_1,
       max(case when seqnum = 2 then item end) as item_2,
       max(case when seqnum = 2 then date end) as date_2,
       max(case when seqnum = 3 then item end) as item_3,
       max(case when seqnum = 3 then date end) as date_3
from (select t.*,
             row_number() over (partition by name order by date asc) as seqnum
      from t
     ) t
group by name;

Upvotes: 2

Related Questions