Quick suggestion, use CROSS JOIN, here is a quick example

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_GROUP') IS NOT NULL DROP TABLE dbo.TBL_GROUP;

CREATE TABLE dbo.TBL_GROUP

(

[Group] CHAR(2) NOT NULL CONSTRAINT PK_DBO_TBL_GROUP_GROUP PRIMARY KEY CLUSTERED

,intensity INT NOT NULL

);

INSERT INTO dbo.TBL_GROUP([Group],intensity)

VALUES

('AA',1120)

,('AB',1400)

,('BA',1800)

,('BB',2240)

,('CA',2800);

SELECT

G.[Group]

,G.intensity

,G.[Group] + G1.[Group] AS OptionX

FROM dbo.TBL_GROUP G

CROSS JOIN dbo.TBL_GROUP G1

WHERE G.intensity <> G1.intensity

ORDER BY G.[Group] ASC;

Results

Group intensity OptionX

----- ----------- -------

AA 1120 AAAB

AA 1120 AABA

AA 1120 AABB

AA 1120 AACA

AB 1400 ABAA

AB 1400 ABBA

AB 1400 ABBB

AB 1400 ABCA

BA 1800 BAAA

BA 1800 BAAB

BA 1800 BABB

BA 1800 BACA

BB 2240 BBAA

BB 2240 BBAB

BB 2240 BBBA

BB 2240 BBCA

CA 2800 CAAA

CA 2800 CAAB

CA 2800 CABA

CA 2800 CABB