FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE subset_data (
   data DATETIME,
  _p VARCHAR(100),
  _t VARCHAR(100),
  _x VARCHAR(100),
);

INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '001', '06:00-12:00', '15');
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '001', '12:00-18:00', '15' );
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '008', '06:00-12:00', '5' );
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '008', '06:00-12:00', '5' );
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '009', '12:00-18:00', '6');
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '010', '06:00-12:00', '12');

SELECT
	_p,
	[00:00-06:00],
	[06:00-12:00],
	[12:00-18:00],
	[18:00-23:59],
	_x 
FROM
	( SELECT _p, _t, _x FROM [dbo].[subset_data] ) d PIVOT ( COUNT ( _t ) FOR _t IN ( [00:00-06:00], [06:00-12:00], [12:00-18:00], [18:00-23:59] ) ) piv 
ORDER BY
	_p;
	
SELECT
	_p,
	COUNT ( CASE _t WHEN '00:00-06:00' THEN 1 END ) AS [00:00-06:00],
	COUNT ( CASE _t WHEN '06:00-12:00' THEN 1 END ) AS [06:00-12:00],
	COUNT ( CASE _t WHEN '12:00-18:00' THEN 1 END ) AS [12:00-18:00],
	COUNT ( CASE _t WHEN '18:00-23:59' THEN 1 END ) AS [18:00-23:59],
	_x 
FROM
	dbo.subset_data sd 
GROUP BY
	_p,
	_x 
ORDER BY
	_p;

_p	06:00-12:00	12:00-18:00	_x
001	1	1	15
008	2	0	5
009	0	1	6
010	1	0	12

_p	06:00-12:00	12:00-18:00	_x
001	1	1	15
008	2	0	5
009	0	1	6
010	1	0	12

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(4 rows affected)

(4 rows affected)