Making multiple rows with few columns into one row with many columns (Oracle view)

Question

What I had, for example, was a table that looks like:

ID varchar2(6)
ERROR_TYPE varchar2(30)
ERROR_CODE varchar2(6)
ERROR_DESCRIPTION varchar2(50)
REVIEWED_FLAG varchar2(1)

The ID is just the typical random ID, ERROR_TYPE is the functionality that caused the error, say an order or a shipment. REVIEWED_FLAG is 'Y' or 'N', ERROR_DESCRIPTION is pretty much just that, and ERROR_CODE is a code such as 1A or 2B with:
0 - it passed/there is no error, or
1 - it failed/there is an error
And the full error code would look like 1A=0, or 2B=1 (not my choice, this is what I was given to deal with).

A standard

select ID, ERROR_CODE from ERROR_TABLE where ERROR_TYPE = 'SHIPMENT'

will return a result set like:

ID      ERROR_CODE  ERROR_TYPE
100001  1A=0        SHIPMENT
100001  2B=1        SHIPMENT
100001  3A=1        SHIPMENT
100001  4B=0        SHIPMENT
100002  1A=1        SHIPMENT
100002  2B=1        SHIPMENT
100002  3A=0        SHIPMENT
100002  4B=0        SHIPMENT

What the user wanted to see was:

ID       ERROR_TYPE  ERROR_CODE_1A  ERROR_CODE_2B  ERROR_CODE_3A  ERROR_CODE_4B
1000001  SHIPMENT          0              1              1              0
1000002  SHIPMENT          1              1              0              0

What I've seen about PIVOT is that it only works with aggregate functions. And this isn't concatenation because the values are in their own columns.

I ended up creating a view that's basically

create view V_SHIPMENT_ERRORS as
with ERROR1A as (select ID, substr(ERROR_CODE, 4) as ERROR_CODE_1A from error_table 
                  where error_type = 'SHIPMENT' and error_code like '1A%'),
with ERROR2B as (select ID, substr(ERROR_CODE, 4) as ERROR_CODE_2B from error_table 
                  where error_type = 'SHIPMENT' and error_code like '2B%'),
with ERROR3A as (select ID, substr(ERROR_CODE, 4) as ERROR_CODE_3A from error_table 
                  where error_type = 'SHIPMENT' and error_code like '3A%'),
with ERROR4B as (select ID, substr(ERROR_CODE, 4) as ERROR_CODE_4B from error_table 
                  where error_type = 'SHIPMENT' and error_code like '4B%')
select a.ID, error_code_1A, error_code_2B, error_code_3A, error_code_4B
  from error1a a, error2b b, error3a c, error4b d
 where a.id = b.id
   and a.id = c.id
   and a.id = d.id;

And this does give me the desired result set.

The basic question - Is there a more efficient way of doing row/column manipulation like this with only SQL?

And a comment to this is if someone is looking to do the same thing I hope what I got to work at least helps them out. I couldn't find this type of solution anywhere and it was a combination of ideas on multiple sites that led to this.

what you can do is split the string into rows. Then manipulation would be easy creating a pivot table. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 9, 2015 at 3:34
There is no string to split, the rows already exist and the effort was to make multiple rows into one. Also the pivot (from what I've read) only works with aggregate functions, such as count. Aggregation is not needed here. — BigFish
– BigFish, Commented Dec 9, 2015 at 3:40

Gordon Linoff · Accepted Answer · 2015-12-09 03:38:40Z

2

You can use conditional aggregation:

select id, shipment_type,
       max(case when error_code like '1A=' then substr(error_code, -1) end) as ec_1a,
       max(case when error_code like '2B=' then substr(error_code, -1) end) as ec_2b,
       max(case when error_code like '3A=' then substr(error_code, -1) end) as ec_3a,
       . . .
from error_table
group by id, shipment_type;

answered Dec 9, 2015 at 3:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

BigFish Over a year ago

I haven't tried this yet but I will. However, my objective was to do what the subject states...multiple rows into one row...

BigFish Over a year ago

Takes a while to get a result set, but the result set is very close to what I was looking for.

Collectives™ on Stack Overflow

Making multiple rows with few columns into one row with many columns (Oracle view)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related