SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE aa (
  a1 varchar(20), -- 日期
  a2 varchar(20), -- 時間
  a3 varchar(20), -- DeviceName
  a4 float        -- pH  
);

INSERT INTO aa VALUES 
('2024-11-12','00:21','100',8.19),
('2024-11-12','03:51','100',8.19),
('2024-11-12','00:21','101',7.31),
('2024-11-12','03:51','101',7.28);

SELECT *
FROM aa;


a1
a2
a3
a4


2024-11-12
00:21
100
8.19

2024-11-12
03:51
100
8.19

2024-11-12
00:21
101
7.31

2024-11-12
03:51
101
7.28


SELECT a1,a2,'pH-'+a3 AS a3,a4
FROM aa;


a1
a2
a3
a4


2024-11-12
00:21
pH-100
8.19

2024-11-12
03:51
pH-100
8.19

2024-11-12
00:21
pH-101
7.31

2024-11-12
03:51
pH-101
7.28


with t1(date, time, device, val) as (
SELECT a1,a2,'pH-'+a3 AS a3,a4
FROM aa
)
select date,time
     , sum(case when device = 'ph-100'
            then val
            else 0
           end) as 'ph-100'
     , sum(case when device = 'ph-101'
            then val
            else 0
           end) as 'ph-101'
  from t1
 group by date,time;


date
time
ph-100
ph-101


2024-11-12
00:21
8.19
7.31

2024-11-12
03:51
8.19
7.28

a1	a2	a3	a4
2024-11-12	00:21	100	8.19
2024-11-12	03:51	100	8.19
2024-11-12	00:21	101	7.31
2024-11-12	03:51	101	7.28

a1	a2	a3	a4
2024-11-12	00:21	pH-100	8.19
2024-11-12	03:51	pH-100	8.19
2024-11-12	00:21	pH-101	7.31
2024-11-12	03:51	pH-101	7.28