0

I have a sample date in 'myTable' table, including one column which represents IDs and second column represents their paired values (something like below sample)

myTable

IDs    values
0      A+
1      A 
1      D
2      
0      A
2      C
1      
3      C

I need to get result as below,

0IDs    1IDs    2IDs    3IDs
A+      -       -       -
-       A       -       -
-       D       -       -
-       -       *       -
A       -       -       -
-       -       C       -
-       *       -       -
-       -       -       C
  • I was just tried to print IDs in one column and values next to the IDs, but I wanted to prints all IDs in multiple columns with their values under, (maybe I should used "related values" instead of "paired values") – αғsнιη Oct 18 '16 at 17:48
1

Here you go...

Data & structure:

create table mytable
(
ids number,
vals varchar(5)
);

insert into mytable values(0, 'A+');
insert into mytable values(1, 'A');
insert into mytable values(1, 'D');
insert into mytable values(2, NULL);
insert into mytable values(0, 'A');
insert into mytable values(2, 'C');
insert into mytable values(1, NULL );
insert into mytable values(3, 'C');

Test:

SQL> select NVL(case when ids=0 then vals else '-' end,'*') as x0IDs,
  2         NVL(case when ids=1 then vals else '-' end,'*') as x1IDs,
  3         NVL(case when ids=2 then vals else '-' end,'*') as x2IDs,
  4         NVL(case when ids=3 then vals else '-' end,'*') as x3IDs
  5  from mytable;

X0IDS X1IDS X2IDS X3IDS
----- ----- ----- -----
A+    -     -     -
-     A     -     -
-     D     -     -
-     -     *     -
A     -     -     -
-     -     C     -
-     *     -     -
-     -     -     C

8 rows selected.

SQL>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.