SQL Rows into Columns

Question

Please help me in converting my rows into columns and not duplicating them. I've been searching and applying for ways to do this but I cannot satisfy my requirement.

create table PROGRAMMER
(
PERSON varchar(15),
LANGUAGE varchar(20),
LEVELS varchar(15)
);

insert into PROGRAMMER (PERSON, LANGUAGE, LEVELS) values ('CARL', 'JAVA', 'SENIOR');
insert into PROGRAMMER (PERSON, LANGUAGE, LEVELS) values ('CARL', 'PHP', 'MID');
insert into PROGRAMMER (PERSON, LANGUAGE, LEVELS) values ('CARL', 'VB.NET', 'JUNIOR');
insert into PROGRAMMER (PERSON, LANGUAGE, LEVELS) values ('GARY', 'C#', 'MID');
insert into PROGRAMMER (PERSON, LANGUAGE, LEVELS) values ('GARY', 'VB.NET', 'MID');
insert into PROGRAMMER (PERSON, LANGUAGE, LEVELS) values ('RALPH', 'PHP', 'SENIOR');
insert into PROGRAMMER (PERSON, LANGUAGE, LEVELS) values ('RALPH', 'RUBY', 'JUNIOR');
insert into PROGRAMMER (PERSON, LANGUAGE, LEVELS) values ('RALPH', 'JAVASCRIPT', 'SENIOR');

I want to represent the table like this using "LEVELS" as the column with some rows having the same person as many times as they have the same levels in different languages. The other columns will be empty if there is no entry.

|  PERSON  |  SENIOR  |    MID    |  JUNIOR  |
----------------------------------------------
|  CARL    |   JAVA   |    PHP    |   VB.NET |
|  GARY    |          |    C#     |          |
|  GARY    |          |   VB.NET  |          |
|  RALPH   |   PHP    |           |  RUBY    |
|  RALPH   |JAVASCRIPT|           |          | 

Thank you.

Answer 1

it is good challenge in designing subqueries, try this query on your table PROGRAMMER as you said:

select t1.person,t1.language as SENIOR,t2.language as MID,t3.language as JUNIOR  
from
(select person,language from PROGRAMMER where levels = 'SENIOR' union all
select distinct person,null as language from PROGRAMMER where person not in(select distinct person from PROGRAMMER where levels = 'SENIOR'))t1,
(select person,language from PROGRAMMER where levels = 'MID' union all
select distinct person,null as language from PROGRAMMER where person not in(select distinct person from PROGRAMMER where levels = 'MID'))t2,
(select person,language from PROGRAMMER where levels = 'JUNIOR' union all
select distinct person,null as language from PROGRAMMER where person not in(select distinct person from PROGRAMMER where levels = 'JUNIOR'))t3
where 
t1.person = t2.person and t1.person = t3.person;

Answer 2

You cannot achieve this with transpose. rather try using natural full outer join

WITH T
    AS (SELECT
             P.*,
             ROW_NUMBER ( )
                 OVER (PARTITION BY PERSON, LEVELS ORDER BY LANGUAGE)
                 R
        FROM
             PROGRAMMER P)
SELECT
      PERSON,
      SENIOR,
      MID,
      JUNIOR
FROM
      (SELECT
            PERSON,
            R,
            LANGUAGE SENIOR
       FROM
            T
       WHERE
            LEVELS = 'SENIOR')
      NATURAL FULL OUTER JOIN (SELECT
                                PERSON,
                                R,
                                LANGUAGE MID
                          FROM
                                T
                          WHERE
                                LEVELS = 'MID')
      NATURAL FULL OUTER JOIN (SELECT
                                PERSON,
                                R,
                                LANGUAGE JUNIOR
                          FROM
                                T
                          WHERE
                                LEVELS = 'JUNIOR');

You get

PERSON          SENIOR               MID                  JUNIOR              
--------------- -------------------- -------------------- --------------------
CARL            JAVA                 PHP                  VB.NET              
GARY                                 C#                                       
GARY                                 VB.NET                                   
RALPH           JAVASCRIPT                                RUBY                
RALPH           PHP                                                           

5 rows selected.

Answer 3

1. You cannot create a column called "LEVEL" in oracle since its a reserved keyword.
2. You create table and insert statements contradict
3. Each person can be associated to more than one language with same level of proficiency. So it is not possible to transpose based on levels.
4. Rather you can try to transpose based on languages.
5. If you have a definitive list of languages then you can use MAX and DECODE
6. PIVOT is not possible to use since it needs an aggregate function on string.!!
7. If you dont have a definitive list then you can use Oracle connect by

Try this if you have definitive list of languages

SELECT
      X.PERSON,
      MAX ( DECODE ( X.LANGUAGE, 'JAVA', X.LEVELS ) ) "JAVA",
      MAX ( DECODE ( X.LANGUAGE, 'PHP', X.LEVELS ) ) "PHP",
      MAX ( DECODE ( X.LANGUAGE, 'VB.NET', X.LEVELS ) ) "VB.NET",
            MAX ( DECODE ( X.LANGUAGE, 'C#', X.LEVELS ) ) "C#",
      MAX ( DECODE ( X.LANGUAGE, 'RUBY', X.LEVELS ) ) "RUBY",
      MAX ( DECODE ( X.LANGUAGE, 'JAVASCRIPT', X.LEVELS ) ) "JAVASCRIPT"
FROM
      (SELECT
            O.PERSON,
            O.LEVELS,
             O.LANGUAGE 
       FROM
            PROGRAMMER O) X
GROUP BY
      X.PERSON;


PERSON          JAVA            PHP             VB.NET          C#             RUBY            JAVASCRIPT     
--------------- --------------- --------------- --------------- -------------- --------------- ---------------
CARL            SENIOR          MID             JUNIOR                         
GARY                                            MID             MID            
RALPH                           SENIOR                                         JUNIOR          SENIOR         

3 rows selected.