I am struggling with this easy looking problem, when I want to transform rows for one id into columns. So there would be X in column , if that ID has row with that value but without using PIVOT as I am not on 11g, so it needs to be some ancient way :) If that is possible as I believe is just not able to crack it on my own. Thank you for help whoever likes challenges and think is easy as it looks like, but is not :/
So if I have
ID Access DB1 DB2 DC1
1 DB1 x 0 0
1 DB2 0 0 0
1 DC1 0 0 x
2 DB1 x 0 0
2 DB2 0 x 0
3 DC1 0 0 x
So the result what I am looking for will be just 3 Rows
ID DB1 DB2 DC1
1 x 0 x
2 x x 0
3 0 0 x
https://data.stackexchange.com/stackoverflow/query/edit/1338592
I am wondering I wouldn't have these 3 other columns and have just first two. What would be the way transform rows into columns. First if there would be exact number so in our case 3 columns DB1, DB2 and DC1 and what if the number wouldn't be know so all distinct row will create new columns. Anybody for challenge.
Input 1
ID Access
1 DB1
1 DB2
1 DC1
2 DB1
2 DB2
3 DC1
Results 1
ID DB1 DB2 DC1
1 DB1 DB2 DC1
2 DB1 DB2
3 DC1
It looks like you want max():
select id, max(db1), max(db2), max(db3)
from t
group by id;