Ricardo Ricardo - 2 months ago 10
SQL Question

Add query results in columns SQL

I have this (result of my query):

id | suk | xpto
----+------+------
4 | 1023 | abc
4 | 1025 | def
4 | 1200 | zzz
4 | 1501 | ppp
5 | 1111 | ola
5 | 2222 | xau


I need transform in

id | suk | suk2 | suk3 | suk4
-----+----------+----------+----------+-----------
4 | 1023 abc | 1025 def | 1200 zzz | 1501 ppp
5 | 1111 ola | 2222 xau | |


Actually, my query is:

SELECT b.ID, s.SKU, s.EVENT_TYPE
FROM VFIE_BONUS_POINTS_RULE b
INNER JOIN VFIE_BONUS_POINTS_RULE_SKU s
ON b.ID = s.ID_BNS_PTS_RL;


I use Oracle.

Is it possible?

Answer

Use pivot and row_number(). Instead of t put your query:

select * 
  from (select row_number() over (partition by id order by suk) rn, t.* from t)
  pivot (max(suk||' '||xpto) suk for rn in (1, 2, 3, 4))

Test:

with t(id, suk, xpto) as (select 4, 1023, 'abc' from dual
                union all select 4, 1025, 'def' from dual
                union all select 4, 1200, 'zzz' from dual
                union all select 4, 1501, 'ppp' from dual
                union all select 5, 1111, 'ola' from dual
                union all select 5, 2222, 'xau' from dual )
select * 
  from (select row_number() over (partition by id order by suk) rn, t.* from t)
  pivot (max(suk||' '||xpto) suk for rn in (1, 2, 3, 4))

Output:

ID  1_SUK     2_SUK     3_SUK     4_SUK
--  --------  --------  --------  --------
 4  1023 abc  1025 def  1200 zzz  1501 ppp
 5  1111 ola  2222 xau