+----------+--------+
| placa | modelo |
+----------+--------+
| 19lc826h | mai |
| 19lc826h | main |
| 19lc826h | power |
| 19en33 | ctr |
| 19en33 | mai |
| 19m35a | ctr |
| 19m35a | mai |
+----------+--------+
The next thing would be to do a series of CTE's:
with ejemplo_2 as
(
select row_number() over(partition by placa order by placa) orden,
placa, modelo
from ejemplo
),
ejemplo_3 as (
select
placa,
case when orden = 1 then modelo else null end panel_1,
case when orden = 2 then modelo else null end panel_2,
case when orden = 3 then modelo else null end panel_3
from ejemplo_2
)
The first CTE will serve to enumerate the order of the models according to the plate and this is achieved by using ROW_NUMBER; After that, the ordering is carried out according to the new column that we created ('order'), the issue with this is that you will get a table of the following form:
This last table would be very inefficient and would not be able to finish completing the solution to your problem, for this I found a solution that can help you and it is the following:
select *
from (
select placa,
panel_1,
lead(panel_2,1) over(partition by placa order by placa) panel_2,
lead(panel_3,2) over(partition by placa order by placa) panel_3
from ejemplo_3
) x
where not x.panel_1 is null
Brief explanation of the above (and last part of the script), you use LEADto get the next row and as a second parameter if you look I put 1 or 2 depending on which row I expect to start bringing the next value.
The final table would be
+----------+---------+---------+---------+
| placa | panel_1 | panel_2 | panel_3 |
+----------+---------+---------+---------+
| 19en33 | ctr | mai | null |
| 19lc826h | mai | main | power |
| 19m35a | ctr | mai | null |
+----------+---------+---------+---------+
Finally, in my opinion PIVOT does not work in this case since the values to fetch are not groupable as if they are values, averages, sums, etc.
The following may help you solve your problem:
First create a replica of the table you have (just some data):
Obtaining by table the following:
The next thing would be to do a series of CTE's:
The first CTE will serve to enumerate the order of the models according to the plate and this is achieved by using ROW_NUMBER; After that, the ordering is carried out according to the new column that we created ('order'), the issue with this is that you will get a table of the following form:
This last table would be very inefficient and would not be able to finish completing the solution to your problem, for this I found a solution that can help you and it is the following:
Brief explanation of the above (and last part of the script), you use LEADto get the next row and as a second parameter if you look I put 1 or 2 depending on which row I expect to start bringing the next value.
The final table would be
Greetings.