How to "horizontalize" multiple lines in oracle

Question

I have a table with this content:

date	user	id	count
11MAR23	a1	10	1024
11MAR23	a1	20	510
11MAR23	a1	40	788
11MAR23	a2	20	300
12MAR23	a1	30	400

and my outcome table should look like this:

date	user	id10	id20	id30	id40
11MAR23	a1	1024	510		788
11MAR23	a2		300
12MAR23	a1			400

the columns are hard coded, no changes anticipated. Just the count of the id´s should be shown in one line instead of multiple lines.

I tried with partitioning, but it displays in different lines like this:

date	user	id10	id20	id30	id40
11MAR23	a1	1024
11MAR23	a1		510

... and so forth ...

Thanks again for your help.

Mike

Answer 1

Collapse the rows using aggregation, such as MAX or SUM, and use DECODE or CASE to selectively decide what to include in each column, like this:

SELECT date,
       usr,
       SUM(DECODE(id,10,count,0)) id10,
       SUM(DECODE(id,20,count,0)) id20,
       SUM(DECODE(id,30,count,0)) id30,
       SUM(DECODE(id,40,count,0)) id40
  FROM table
 GROUP BY date,
          usr

OR

SELECT date,
       usr,
       MAX(DECODE(id,10,count)) id10,
       MAX(DECODE(id,20,count)) id20,
       MAX(DECODE(id,30,count)) id30,
       MAX(DECODE(id,40,count)) id40
  FROM table
 GROUP BY date,
          usr

Except don't use COUNT or DATE as column names, they are reserved keywords.