Problem pivoting table

[abenstex]

Hi all,

i have a table with the following structure:

entry_no ID item amount
1 123 item1 2
2 123 item2 1
3 123 item3 3
4 456 item1 1
5 456 item4 4

Now what i would like to achieve is to create a view that returns the data in the following form:
ID item1 item2 item3 item4
123 2 1 3
456 1 4

The problem is though that i do not know for sure how many items are associated with an id, it might be zero or ten...

Thanks for any help!

 

[SantaMufasa]

One method to achieve your results is:

select * from abenstex;

  ENTRY_NO         ID ITEM      AMOUNT
---------- ---------- ----- ----------
         1        123 item1          2
         2        123 item2          1
         3        123 item3          3
         4        456 item1          1
         5        456 item4          4

5 rows selected.

select id
      ,max(item1) item1
      ,max(item2) item2
      ,max(item3) item3
      ,max(item4) item4
  from (select id, amount item1, null item2, null item3, null item4
          from abenstex where item = 'item1'
         union
        select id, null, amount, null, null
          from abenstex where item = 'item2'
         union
        select id, null, null, amount, null
          from abenstex where item = 'item3'
         union
        select id, null, null, null, amount
          from abenstex where item = 'item4'
       )
 group by id;

        ID      ITEM1      ITEM2      ITEM3      ITEM4
---------- ---------- ---------- ---------- ----------
       123          2          1          3
       456          1                                4

2 rows selected.
********************************************************************

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[abenstex]

I'm sorry if i was unclear in my original post, but the problem is that item1, item2 could actually be any arbitrary string like 'abc', 'sausage', 'airplane'...

 

[SantaMufasa]

Then if the item names are unpredictable/arbritrary, and if you choose to use the above coding technique, you will need to compose the query using a method named, "(PL)SQL-writing-SQL".

Are you familiar with that? (If not, then we can post an example, tailored to your need.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

Pivoting can be done in SQL if there is a finite number of columns to pivot, say 5 or 10, and then one of the analytical functions such as Rank() or row_number() can be used to assign an arbitrary position that can pivot to column 1,2,3, etc.... If this is the case, explain and an example can be produced.

 

[abenstex]

Would it be possible then to update the table to include a iterative value per ID like

ID iteration ...
123 1 ...
123 2 ...
123 3 ...
456 1 ...
456 2 ...

 

[cmmrfrds]

Yes, a Select Statement can be used in an Update. You create the iterative value in a select statement which is used in the update statement.

 

[abenstex]

I thought so But my problem right now is how does that select statement have to look like?

 

[Dagon]

Something like this would probably work, but I'm not sure how it helps with your pivot problem.

update items i1 set iteration = 
(select iteration from
(select item, rowid, rank() over (partition by item order by rowid) as iteration
from items) i2
where i2.item = i1.item
and   i2.rowid = i1.rowid)

 

[taupirho]

select id,item,amount
from t

ID I AMOUNT
---------- - ----------
123 X 2
123 Y 1
123 Z 3
456 X 1
456 A 4


select id,item,amount,
row_number() over(partition by id order by id) iteration
from t

ID I AMOUNT ITERATION
---------- - ---------- ----------
123 X 2 1
123 Y 1 2
123 Z 3 3
456 X 1 1
456 A 4 2




In order to understand recursion, you must first understand recursion.

 

[abenstex]

Thanks for your answers! I forgot about this row_number() functions.