PIVOT SQL SERVER issue

Question

I need some help in implementing SQL PIVOT in my data.

here is query :

select a.id_mcm AS ID,b.nama_mcm AS Name,c.nama_ctr AS Place,a.tgl_mutasi AS Move_On
from mcmmutasi as a
left join mcmmcm as b on b.id_mcm=a.id_mcm
left join mcmcenter as c on c.kode_ctr=a.kode_ctr
where a.id_mcm='ATP211004131604'
order by a.tgl_mutasi asc ,b.nama_mcm asc

and here is the output of it :

ID	Name	Place	Move_On
ATP211004131604	Sumartini	2021-10-04 00:00:00.000	Antapani
ATP211004131604	Sumartini	2021-12-03 14:33:19.450	Cikutra
ATP211004131605	Person	2021-10-06 00:00:00.000	Place A
ATP211004131605	Person	2021-10-10 00:00:00.000	Place B
ATP211004131605	Person	2021-12-08 14:33:19.450	Place C
ATP211004131605	Person	2021-12-23 14:33:19.450	Place D

and what im trying to do is to make the 2 Rows become 1. so this is the desire table that i want to achieve it:

ID	Name	Place 1	Place 2	Place 3	Place 4	Move_On(Last One)
ATP211004131604	Sumartini	Antapani	Cikutra	-	-	2021-10-04 00:00:00.000
ATP211004131605	Person	Place A	Place B	Place C	Place D	2021-12-23 14:33:19.450

so its easy to read for user to know the history of Person History/Log.

and i tried to use PIVOT SQLServer while reading and learn about it

but im facing some Errors and completely dont know how to use it.

so can anyone help me and telling me how to achieve that.

here is my Code of PIVOT Query :

WITH pivot_data AS
(
select a.id_mcm as id ,b.nama_mcm as name , c.nama_ctr as Place,a.tgl_mutasi as Move_On
from mcmmutasi as a
left join mcmmcm as b on b.id_mcm=a.id_mcm
left join mcmcenter as c on c.kode_ctr=a.kode_ctr
where a.id_mcm='ATP211004131604'
)
SELECT  name,place,move_on
FROM pivot_data
PIVOT (max(id) FOR id IN (name,place,move_on)) AS p;

---

Update :

"Table where i store history of user move"
CREATE TABLE mcmmutasi 
(
 id_mcm VARCHAR(64)
 , tgl_mutasi DATETIME
 , kode_ctr INT
);

"Table Master (user)
CREATE TABLE mcmmcm
(
  id_mcm VARCHAR(64),
  nama_mcm VARCHAR(64)
);

"Table Master place
CREATE TABLE mcmcenter
(
  kode_ctr INT,
  nama_ctr VARCHAR(64)
);

 INSERT INTO mcmmutasi
 VALUES
 ('ATP211004131604', '2021-10-04 00:00:00.000', 13)
 , ('ATP211004131604', '2021-12-03 14:33:19.450', 23)
 , ('ATP211004131605', '2021-10-06 00:00:00.000', 33)
 , ('ATP211004131605', '2021-12-08 14:33:19.450', 43)
 , ('ATP211004131605', '2021-10-10 00:00:00.000', 53)
 , ('ATP211004131605', '2021-12-23 14:33:19.450', 63);

 INSERT INTO mcmmcm
 VALUES
 ('ATP211004131604', 'Sumartini')
 , ('ATP211004131605', 'Person');

 INSERT INTO mcmcenter
 VALUES
 (  13, 'Antapani')
 , (23, 'Cikutra')
 , (33, 'Place A')
 , (43, 'Place C')
 , (53, 'Place B')
 , (63, 'Place D');

as u can see, there is a user moved more than 2 places.

I want my final results to look like this ( if it possible):
+-------+-----------+-----------+--------------------------------------------------+
| id | name    |  Palce 1 | place 2 | Place 3 | place 4 |  Move_On (Only Last One) |
+-------+-----------+-----------+--------------------------------------------------+
| 1  | Person  | Place A  | Place B | Place C | Place D |  20-11-2023              |
| 5  |Sumartini| Antapani | Cikutra |  -      |   -     | 214-555-1234             |
+-------+-----------+-----------+--------------------------------------------------+

Answer 1

Pivot isn't well suited to this as it appears that you need both MIN() and MAX(), so instead use group by e.g:

SELECT
      a.id_mcm AS ID
    , b.nama_mcm AS Name
    , min(c.nama_ctr) as old_place
    , (case when max(c.nama_ctr) = min(c.nama_ctr) then NULL else max(c.nama_ctr) end) as new_place
    , max(a.tgl_mutasi) AS Move_On
FROM mcmmutasi AS a
LEFT JOIN mcmmcm AS b ON b.id_mcm = a.id_mcm
LEFT JOIN mcmcenter AS c ON c.kode_ctr = a.kode_ctr
WHERE a.id_mcm = 'ATP211004131604'
GROUP BY a.id_mcm ASC
    , b.nama_mcm ASC
ORDER BY a.id_mcm ASC
    , b.nama_mcm ASC

However if there are more than 2 rows for any selection, then this group by result may not be what you were hoping for. It may be necessary to provide more complex sample data (and the expected result from that more complex data).