SQL Server 2017

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

CREATE TABLE TypeGroupTest (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
Xvalue int NULL , 
Xmin int NULL ,
Xmax int NULL); 

INSERT INTO TypeGroupTest (XTypeNo,Xtype,Xvalue,Xmin,Xmax)
VALUES 
(1,'G1Data',98,100,200),
(2,'G1Data',99,100,200),
(3,'G1Data',100,100,200),
(4,'G1Data',101,100,200),
(5,'G1Data',102,100,200),
(6,'G1Data',103,100,200),
(7,'G1Data',104,100,200),
(8,'G1Data',105,100,200),
(9,'G1Data',106,100,200),
(10,'G1Data',107,100,200),
(11,'G1Data',108,100,200),
(12,'G1Data',109,100,200),
(13,'G1Data',110,100,200),
(14,'G1Data',111,100,200),
(15,'G1Data',112,100,200),
(16,'G1Data',113,100,200),
(17,'G1Data',114,100,200),
(18,'G1Data',115,100,200),
(19,'G1Data',116,100,200),
(20,'G1Data',117,100,200),
(21,'G2Data',190,100,200),
(22,'G2Data',191,100,200),
(23,'G2Data',192,100,200),
(24,'G2Data',193,100,200),
(25,'G2Data',194,100,200),
(26,'G2Data',195,100,200),
(27,'G2Data',196,100,200),
(28,'G2Data',197,100,200),
(29,'G2Data',198,100,200),
(30,'G2Data',199,100,200),
(31,'G2Data',200,100,200),
(32,'G2Data',201,100,200),
(33,'G2Data',202,100,200),
(34,'G2Data',203,100,200),
(35,'G2Data',204,100,200),
(36,'G2Data',205,100,200),
(37,'G2Data',206,100,200),
(38,'G2Data',207,100,200),
(39,'G2Data',208,100,200),
(40,'G2Data',209,100,200),
(41,'G3Data',197,0,200),
(42,'G3Data',198,0,200),
(43,'G3Data',199,0,200),
(44,'G3Data',200,0,200),
(45,'G3Data',201,0,200),
(46,'G3Data',202,0,200),
(47,'G3Data',203,0,200),
(48,'G3Data',204,0,200),
(49,'G3Data',205,0,200),
(50,'G3Data',206,0,200),
(51,'G4Data',150,100,200),
(52,'G4Data',151,100,200),
(53,'G4Data',152,100,200),
(54,'G4Data',153,100,200),
(55,'G4Data',154,100,200),
(56,'G4Data',155,100,200);
SELECT *
FROM TypeGroupTest;


XTypeNo
Xtype
Xvalue
Xmin
Xmax


1
G1Data
98
100
200

2
G1Data
99
100
200

3
G1Data
100
100
200

4
G1Data
101
100
200

5
G1Data
102
100
200

6
G1Data
103
100
200

7
G1Data
104
100
200

8
G1Data
105
100
200

9
G1Data
106
100
200

10
G1Data
107
100
200

11
G1Data
108
100
200

12
G1Data
109
100
200

13
G1Data
110
100
200

14
G1Data
111
100
200

15
G1Data
112
100
200

16
G1Data
113
100
200

17
G1Data
114
100
200

18
G1Data
115
100
200

19
G1Data
116
100
200

20
G1Data
117
100
200

21
G2Data
190
100
200

22
G2Data
191
100
200

23
G2Data
192
100
200

24
G2Data
193
100
200

25
G2Data
194
100
200

26
G2Data
195
100
200

27
G2Data
196
100
200

28
G2Data
197
100
200

29
G2Data
198
100
200

30
G2Data
199
100
200

31
G2Data
200
100
200

32
G2Data
201
100
200

33
G2Data
202
100
200

34
G2Data
203
100
200

35
G2Data
204
100
200

36
G2Data
205
100
200

37
G2Data
206
100
200

38
G2Data
207
100
200

39
G2Data
208
100
200

40
G2Data
209
100
200

41
G3Data
197
0
200

42
G3Data
198
0
200

43
G3Data
199
0
200

44
G3Data
200
0
200

45
G3Data
201
0
200

46
G3Data
202
0
200

47
G3Data
203
0
200

48
G3Data
204
0
200

49
G3Data
205
0
200

50
G3Data
206
0
200

51
G4Data
150
100
200

52
G4Data
151
100
200

53
G4Data
152
100
200

54
G4Data
153
100
200

55
G4Data
154
100
200

56
G4Data
155
100
200


CREATE TABLE ExpectData (
Xtype nvarchar(40) NULL , 
XGroup nvarchar(40) NULL); 
-- 以上測試資料集，預期分類資料狀況
INSERT INTO ExpectData (Xtype,XGroup)
VALUES 
('G1Data','G1'),
('G2Data','G2'),
('G3Data','G3'),
('G4Data','G4');
SELECT * 
FROM ExpectData


Xtype
XGroup


G1Data
G1

G2Data
G2

G3Data
G3

G4Data
G4


SELECT Xtype,G1,G2,G3,
CASE 
WHEN G3 <> 0 THEN 'G3'
WHEN G3 = 0 AND G1 <> 0 THEN 'G1'
WHEN G3 = 0 AND G1 = 0 AND G2 <> 0 THEN 'G2'
WHEN G3 = 0 AND G1 = 0 AND G2 = 0 THEN NULL
END AS XGROUP_NEW
FROM (
SELECT Xtype,
SUM(CASE WHEN XGROUP='G1' THEN TempValue ELSE 0 END) AS 'G1',
SUM(CASE WHEN XGROUP='G2' THEN TempValue ELSE 0 END) AS 'G2',
SUM(CASE WHEN XGROUP='G3' THEN TempValue ELSE 0 END) AS 'G3'
FROM (
SELECT Xtype,1 AS TempValue,
CASE
  WHEN Xvalue <= Xmin
  THEN 'G1'
  WHEN Xvalue >= Xmax
  THEN 'G3'
  WHEN Xvalue > Xmin AND Xvalue < Xmax
  THEN 'G2'
END AS XGROUP
FROM TypeGroupTest) AS TempA
GROUP BY Xtype
) AS TempB
ORDER BY Xtype


Xtype
G1
G2
G3
XGROUP_NEW


G1Data
3
17
0
G1

G2Data
0
10
10
G3

G3Data
0
3
7
G3

G4Data
0
6
0
G2

XTypeNo	Xtype	Xvalue	Xmin	Xmax
1	G1Data	98	100	200
2	G1Data	99	100	200
3	G1Data	100	100	200
4	G1Data	101	100	200
5	G1Data	102	100	200
6	G1Data	103	100	200
7	G1Data	104	100	200
8	G1Data	105	100	200
9	G1Data	106	100	200
10	G1Data	107	100	200
11	G1Data	108	100	200
12	G1Data	109	100	200
13	G1Data	110	100	200
14	G1Data	111	100	200
15	G1Data	112	100	200
16	G1Data	113	100	200
17	G1Data	114	100	200
18	G1Data	115	100	200
19	G1Data	116	100	200
20	G1Data	117	100	200
21	G2Data	190	100	200
22	G2Data	191	100	200
23	G2Data	192	100	200
24	G2Data	193	100	200
25	G2Data	194	100	200
26	G2Data	195	100	200
27	G2Data	196	100	200
28	G2Data	197	100	200
29	G2Data	198	100	200
30	G2Data	199	100	200
31	G2Data	200	100	200
32	G2Data	201	100	200
33	G2Data	202	100	200
34	G2Data	203	100	200
35	G2Data	204	100	200
36	G2Data	205	100	200
37	G2Data	206	100	200
38	G2Data	207	100	200
39	G2Data	208	100	200
40	G2Data	209	100	200
41	G3Data	197	0	200
42	G3Data	198	0	200
43	G3Data	199	0	200
44	G3Data	200	0	200
45	G3Data	201	0	200
46	G3Data	202	0	200
47	G3Data	203	0	200
48	G3Data	204	0	200
49	G3Data	205	0	200
50	G3Data	206	0	200
51	G4Data	150	100	200
52	G4Data	151	100	200
53	G4Data	152	100	200
54	G4Data	153	100	200
55	G4Data	154	100	200
56	G4Data	155	100	200