SQL Server 2019

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

-- ----------------------------
-- Table structure for tbl_cco
-- ----------------------------
DROP TABLE IF EXISTS [dbo].[tbl_cco];

CREATE TABLE [dbo].[tbl_cco] (
  [Total] int NULL,
  [Total_X] int NULL,
  [Total_Y] int NULL,
  [Total_Z] int NULL,
  [DateHour] datetime NULL,
  [Cod] varchar(255) NULL
);

-- ----------------------------
-- Records of tbl_cco
-- ----------------------------
INSERT INTO [dbo].[tbl_cco] ([Total], [Total_X], [Total_Y], [Total_Z], [DateHour], [Cod]) VALUES (N'27', N'1', N'2', N'1', N'2025-06-01 09:00:00.000', N'PLRM');
INSERT INTO [dbo].[tbl_cco] ([Total], [Total_X], [Total_Y], [Total_Z], [DateHour], [Cod]) VALUES (N'22', N'5', N'3', N'0', N'2025-06-01 09:00:00.000', N'CTNA');
INSERT INTO [dbo].[tbl_cco] ([Total], [Total_X], [Total_Y], [Total_Z], [DateHour], [Cod]) VALUES (N'23', N'0', N'1', N'0', N'2025-06-01 21:00:00.000', N'PLRM');
INSERT INTO [dbo].[tbl_cco] ([Total], [Total_X], [Total_Y], [Total_Z], [DateHour], [Cod]) VALUES (N'18', N'3', N'1', N'0', N'2025-06-01 21:00:00.000', N'CTNA');

-- ----------------------------
-- Indexes structure for table tbl_cco
-- ----------------------------
CREATE UNIQUE NONCLUSTERED INDEX [CodCo]
ON [dbo].[tbl_cco] (
  [DateHour] ASC,
  [Cod] ASC
);

SELECT * FROM [dbo].[tbl_cco];

WITH cte AS (
    SELECT DISTINCT
		[Total] AS Total, 
		[Total_X] AS Total_X,
		[Total_Y] AS Total_Y,
		[Total_Z] AS Total_Z, 
		[Cod],
		[DateHour] 
		FROM [dbo].[tbl_cco]
)
SELECT DISTINCT
		[DateHour],
		-- plrm
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_X ELSE 0 END) AS [T_X_P],
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_Y ELSE 0 END) AS [T_Y_P],
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_Z ELSE 0 END) AS [T_Z_P],	
		SUM(CASE WHEN Cod = 'PLRM' THEN Total ELSE 0 END) AS [T_P],		
		
		-- ctna
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_X ELSE 0 END) AS [T_X_C],
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_Y ELSE 0 END) AS [T_Y_C],
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_Z ELSE 0 END) AS [T_Z_C],
		SUM(CASE WHEN Cod = 'CTNA' THEN Total ELSE 0 END) AS [T_C],
		
		-- super total
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_X ELSE 0 END) +
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_X ELSE 0 END) AS [T_X],
		
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_Y ELSE 0 END) +
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_Y ELSE 0 END) AS [T_Y],
		
		SUM(CASE WHEN Cod = 'PLRM' THEN Total_Z ELSE 0 END) +
		SUM(CASE WHEN Cod = 'CTNA' THEN Total_Z ELSE 0 END) AS [T_Z],
		
		SUM(CASE WHEN Cod = 'PLRM' THEN Total ELSE 0 END) + 
		SUM(CASE WHEN Cod = 'CTNA' THEN Total ELSE 0 END) AS [S_Total]	
				
FROM cte;

Total	Total_X	Total_Y	Total_Z	DateHour	Cod
27	1	2	1	2025-06-01 09:00:00.000	PLRM
22	5	3	0	2025-06-01 09:00:00.000	CTNA
23	0	1	0	2025-06-01 21:00:00.000	PLRM
18	3	1	0	2025-06-01 21:00:00.000	CTNA

Msg 8120 Level 16 State 1 Line 34
Column 'cte.DateHour' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.