Pivot multiple rows (2 columns) into a single row-CodePudding

I have a table where it has only 2 columns, the first columns is a name identifier and the second column is a value for this identifier (basically the table acts as default values), below is a screenshot of that table.

What I want is to convert the table from multiple rows into a single row and the values would be columns with the first column as column name. Example, the current values to be transformed into the below.

I read about the PIVOT operator, however it requires an aggregate function in the pivot clause but I don't think I can use an aggregate function in this case, its just setting row values as column values. Is this possible with PIVOT or is there another construct I should use to achieve this?

CodePudding user response：

You can use pivot clause for that purpose, like below (Your table has only 2 columns and I assume you don't have any duplicate code)

select *
from Yourtable
pivot (
max(value) for code in (
        'AGE' as AGE
    ,   'FIRST_NAME' as FIRST_NAME
    ,   'LAST_NAME' as LAST_NAME
    ,   'STATUS' as STATUS
    )
)

CodePudding user response：

There is already a correct technical answer, showing how to pivot in your case.

Let me explain why this "pivoting" is indeed an aggregation, at a logical level.

You have a group of four rows, and you want to generate a "summary row" for the group. (Imagine, in parallel, that you had several employees identified by employee id, in an additional column; each employee had up to four rows, for the same attributes. Then you are grouping by employee id, each group has up to four rows - fewer if there are missing attributes - and you want to get a "summary row" for each group.)

This is a form of aggregation. But for what aggregate function? You seem to only have one value for AGE, only one value for STATUS, etc.

In fact, you can think of AGE as existing in each of the four rows. When the CODE is 'AGE' then the value is 42, and when the CODE is something else then the value is NULL. You could use SUM(), AVG(), MIN(), MAX() over these four values (one is 42, the rest are NULL); they would all return the same answer, 42 - since all aggregate functions ignore NULL.

What if the values are strings, not numbers? Answer: same thing - except you can't use SUM() or AVG(). You still have MIN() and MAX(). In fact you could use other aggregate function too - they just have to be string aggregates. For example you could use LISTAGG(). Again, you are aggregating a single non-NULL string, the others are NULL, so the result will be just that one non-NULL string.

Before Oracle introduced the PIVOT operator in version 11.1 of the database, programmers were already able to pivot - using a conditional aggregation just like I explained. Something like

select max(case when code = 'AGE' then AGE end) as AGE,
       ...
from   ...
group  by EMPLOYEE_ID   -- in the more general case

(in your simple case you don't need to group by anything.)