select two values in one column

Question

I have three tables:

MMP_USER_METADATA_INSTANCE

+----+---------------------------+------------------+-------------+---------+
| ID | INSTANCE_VALUE_VISIBILITY |       NAME       | METADATA_ID | USER_ID |
+----+---------------------------+------------------+-------------+---------+
|  3 |                         1 | dados-adicionais |           1 |       1 |
+----+---------------------------+------------------+-------------+---------+

MMP_USER_METADATA_INST_VALUE

+-------+--------+------------------+---------------------------+------------------+
|  ID   | VALUE  | METADATAENTRY_ID | USER_METADATA_INSTANCE_ID | ELEMENT_POSITION |
+-------+--------+------------------+---------------------------+------------------+
| 1,288 | Testes |               50 |                         3 |                0 |
| 1,289 | 16     |               51 |                         3 |                1 |
| 1,290 | Teste  |               52 |                         3 |                2 |
| 1,291 |        |               53 |                         3 |                3 |
| 1,292 | 44     |               54 |                         3 |                4 |
| 1,293 | 38     |               55 |                         3 |                5 |
| 1,294 |        |               56 |                         3 |                6 |
| 1,295 | Teste  |               57 |                         3 |                7 |
| 1,296 | 46     |               58 |                         3 |                8 |
| 1,297 | 46     |               59 |                         3 |                9 |
| 1,298 | Teste  |               60 |                         3 |               10 |
| 1,299 |        |               61 |                         3 |               11 |
| 1,300 | Teste  |               62 |                         3 |               12 |
| 1,301 | teste  |               63 |                         3 |               13 |
| 1,302 | teste  |               64 |                         3 |               14 |
+-------+--------+------------------+---------------------------+------------------+

MMP_METADATA_ENTRY

+----+-----------+--------------------------+----------+------------+----------------+-------------+------------------+
| ID | INDEXABLE |           NAME           | REQUIRED | VALUE_TYPE | DOMAIN_LIST_ID | METADATA_ID | ELEMENT_POSITION |
+----+-----------+--------------------------+----------+------------+----------------+-------------+------------------+
| 50 |         0 | Skype                    |        0 |          4 | null           |           1 |                0 |
| 51 |         0 | Centro de Custo          |        1 |          7 | 32             |           1 |                1 |
| 52 |         0 | Endereço Bairro          |        0 |          2 | null           |           1 |                2 |
| 53 |         0 | Endereço Cidade          |        0 |          2 | null           |           1 |                3 |
| 54 |         0 | Estado Civil             |        1 |          7 | 34             |           1 |                4 |
| 55 |         0 | Filhos                   |        1 |          7 | 33             |           1 |                5 |
| 56 |         0 | Graduação Curso          |        0 |          2 | null           |           1 |                6 |
| 57 |         0 | Graduação Faculdade      |        0 |          2 | null           |           1 |                7 |
| 58 |         0 | Nivel de Idioma Inglês   |        1 |          7 | 1              |           1 |                8 |
| 59 |         0 | Nivel de Idioma Espanhol |        1 |          7 | 1              |           1 |                9 |
| 60 |         0 | Outro Idioma             |        0 |          2 | null           |           1 |               10 |
| 61 |         0 | Link Linkedin            |        0 |          3 | null           |           1 |               11 |
| 62 |         0 | Link Facebook            |        0 |          3 | null           |           1 |               12 |
| 63 |         0 | Esporte                  |        0 |          4 | null           |           1 |               13 |
| 64 |         0 | Time de Futebol          |        0 |          4 | null           |           1 |               14 |
+----+-----------+--------------------------+----------+------------+----------------+-------------+------------------+

I make this join:

SELECT i.USER_ID
    ,m_e.NAME
    ,v.VALUE
FROM MMP_USER_METADATA_INSTANCE i
LEFT JOIN MMP_USER_METADATA_INST_VALUE v ON (i.id = v.USER_METADATA_INSTANCE_ID)
LEFT JOIN MMP_METADATA_ENTRY m_e ON (v.METADATAENTRY_ID = m_e.id)
WHERE i.USER_ID = 1

Result from the query above:

+---------+--------------------------+--------+
| USER_ID |           NAME           | VALUE  |
+---------+--------------------------+--------+
|       1 | Skype                    | Testes |
|       1 | Centro de Custo          | 16     |
|       1 | Endereço Bairro          | Teste  |
|       1 | Endereço Cidade          |        |
|       1 | Estado Civil             | 44     |
|       1 | Filhos                   | 38     |
|       1 | Graduação Curso          |        |
|       1 | Graduação Faculdade      | Teste  |
|       1 | Nivel de Idioma Inglês   | 46     |
|       1 | Nivel de Idioma Espanhol | 46     |
|       1 | Outro Idioma             | Teste  |
|       1 | Link Linkedin            |        |
|       1 | Link Facebook            | Teste  |
|       1 | Esporte                  | teste  |
|       1 | Time de Futebol          | teste  |
+---------+--------------------------+--------+

Expected Output:

+---------+---------------+----------------------+-------------------------+---------------------+-------------------+-------------+---------------------+-----------------------------+-----------------------------+-------------------------------+----------------------+-------------------+-----------------------+-----------------+-------------------------+
| USER_ID |       1       |          2           |            3            |          4          |         5         |      6      |          7          |              8              |              9              |              10               |          11          |        12         |          13           |       14        |           15            |
+---------+---------------+----------------------+-------------------------+---------------------+-------------------+-------------+---------------------+-----------------------------+-----------------------------+-------------------------------+----------------------+-------------------+-----------------------+-----------------+-------------------------+
|       1 | Skype =Testes | Centro de Custo = 16 | Endereço Bairro = Teste | Endereço Cidade =   | Estado Civil = 44 | Filhos = 38 | Graduação Curso =   | Graduação Faculdade = Teste | Nivel de Idioma Inglês = 46 | Nivel de Idioma Espanhol = 46 | Outro Idioma = Teste | Link Linkedin =   | Link Facebook = Teste | Esporte = teste | Time de Futebol = teste |
+---------+---------------+----------------------+-------------------------+---------------------+-------------------+-------------+---------------------+-----------------------------+-----------------------------+-------------------------------+----------------------+-------------------+-----------------------+-----------------+-------------------------+

Answer 1

You are essentially doing a pivot on the data. The following is ANSI SQL and will work on all modern DBMS.

with data as (
  -- this is essentially your current query
  SELECT i.user_id,
         m_e.name,
         v.value, 

         -- this just gives every row a unique number
         row_number() over (partition by i.user_id order by m_e.name) as rn
  FROM mmp_user_metadata_instance i
    LEFT JOIN mmp_user_metadata_inst_value v ON (i.id = v.user_metadata_instance_id)
    LEFT JOIN mmp_metadata_entry m_e ON (v.metadataentry_id = m_e.id)
  WHERE i.user_id = 1
)
select user_id,
       max(case when rn =  1 then name||' = '||value end) as "1",
       max(case when rn =  2 then name||' = '||value end) as "2",
       max(case when rn =  3 then name||' = '||value end) as "3",
       max(case when rn =  4 then name||' = '||value end) as "4",
       max(case when rn =  5 then name||' = '||value end) as "5",
       max(case when rn =  6 then name||' = '||value end) as "6",
       max(case when rn =  7 then name||' = '||value end) as "7",
       max(case when rn =  8 then name||' = '||value end) as "8",
       max(case when rn =  9 then name||' = '||value end) as "9",
       max(case when rn = 10 then name||' = '||value end) as "10",
       max(case when rn = 11 then name||' = '||value end) as "11",
       max(case when rn = 12 then name||' = '||value end) as "12",
       max(case when rn = 13 then name||' = '||value end) as "13",
       max(case when rn = 14 then name||' = '||value end) as "14",
       max(case when rn = 15 then name||' = '||value end) as "15"
from data
group by user_id;

If value can be null you need to wrap that into a coalesce() to return an empty string, otherwise the string concatenation in the "pivot" part will yield a null value as well.

You will have to define an upper limit on the number of columns. The SQL language requires the number of columns for a query to be defined before running it, so there is no way you can make those columns dynamic.