0

I have a table as follow:

+---+---+---+ |obj|col|Val| +---+---+---+ |1 |c1 | v1| +---+---+---+ |1 |c2 | v2| +---+---+---+ |2 |c1 | v3| +---+---+---+ |2 |c2 | v4| +---+---+---+

And I am looking for SQL that will give the result in the following format

+---+---+---+ |obj|c1 |c2 | +---+---+---+ |1 |v1 | v2| +---+---+---+ |2 |v3 | v4| +---+---+---+
Improve this question

2 Answers 2

Reset to default
1

In this SQL, I am checking for col = 'c?' and printing out the corresponding Val. But the reason for group by is to avoid all NULL values in case the condition doesn't match. By grouping on obj all the NULL values will be avoided and produce the desired result.

SELECT obj, MAX( CASE WHEN col = 'c1' THEN Val END ) AS c1, MAX( CASE WHEN col = 'c2' THEN Val END ) AS c2 FROM Table GROUP BY obj;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

First you need to select all the unique id from your table

select distinct id from a_table_you_did_not_name

how you can use that to left join to your columns

select base.id, one.val as c1, two.val as c2 from ( select distinct id from a_table_you_did_not_name ) base left join a_table_you_did_not_name one on one.id = base.id and one.col = 'c1' left join a_table_you_did_not_name two on two.id = base.id and two.col = 'c2'

note: your case is a relatively simple case of this kind of join -- I coded it like this because using my method can be extended to the more complicated cases and still work. There are some other ways for this particular requirement that might be simpler.

specifically the most common one is joining to multiple tables, not all in the same table. My method will still work in those cases.

Improve this answer

7 Comments

This is most definitely not the way to pivot data in SQL, and in Oracle. The other Answer (marked Correct) shows the most general solution. In Oracle 11 and above, there is also the PIVOT clause. No joins are needed for pivoting.
@mathguy - there is no internal difference between doing it this way and using the PIVOT clause. And the other answer is not the most general solution consider the case where you have two tables and you want a result with 5 columns. Mine extends to that case and Teja's does not.
Not sure what you mean by "internal difference", I am not familiar with that concept. But the difference is that your solution reads the base table three times, instead of just once with the standard PIVOT solution. (Are you denying that?) So you do a lot of unnecessary and expensive I/O. And the other solution IS the most general solution for pivoting. Your solution is not "pivoting"; it is more general, in the sense you stated it, but that solves a problem the OP didn't have in this thread.
Yes I am denying that -- I believe this query a pivot will work exactly the same if you have two tables (which is the much more common case) as I said in the yellow comment "There are some other ways for this particular requirement that might be simpler." Remember also that a GROUP BY has horrible performance compared to an indexed join in many cases (eg large and sparse). Group by requires a lot of memory and processing.
You are missing my point. PIVOT reads the table only once, your joins read the table three times. You say you are denying that (and if you are, just check execution plans to see for yourself), but then you give a reason that has nothing to do with this.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.