CODEWITHSUNDEEP
sqloracle-databaseself-join
Matt A
Matt A

Reputation: 3

Should I do a self join

I have a table that is storing the information in a very strange manner. Each entry has 4 rows in the database. They are linked by a ROW_ID field each entry then has a column_id between 1-4 which signifies the column that the value goes in 

Row_id          Column_id         Value
1               1                 Value1
1               2                 Value2
1               3                 Value3
1               4                 Value4

This information is currently being pulled out with a query for each value that gets put into a excel spread sheet but it is taking 4 separate queries that have the information for each column 

select value from table where column_id =1 order by row_id;
select value from table where column_id =2 order by row_id;
select value from table where column_id =3 order by row_id;
select value from table where column_id =4 order by row_id;

Can this all be output as a single line? 

        Column_id1    Column_id2     Column_id3     Column_id4
Row_id  Value1        Value2         Value3         Value4

Upvotes: 0

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can use conditional aggregation, self-join, or pivot. I prefer the former:

select row_id,
       max(case when column_id = 1 then value end) as value_1,
       max(case when column_id = 2 then value end) as value_2,
       max(case when column_id = 3 then value end) as value_3,
       max(case when column_id = 4 then value end) as value_4
from t
group by row_id;

Upvotes: 1

Related Questions