How to get all sorts of unique pairs of two fields from one table?

Question

SQL

There is such a table:

create table EMPLOYEE(
PK_ID int identity(1,1) NOT NULL,
DEPARTMENT_ID int NOT NULL,
FK_CHIEF_ID int NOT NULL,
NAME varchar(255) NOT NULL,
POSITION varchar(255) NOT NULL,
SALARY float NOT NULL,

constraint PK_ID_ID primary key (PK_ID),
constraint FK_CHIEF_ID_EMPLOYEE foreign key (FK_CHIEF_ID) references EMPLOYEE (PK_ID),
)

We fill it with data:

INSERT INTO EMPLOYEE VALUES 
(2, 1, 'NAT','programmer',145.9),
(2, 1, 'IVA','programmer',200.1),
(2, 1, 'EVG','boss',300.0),
(2, 1, 'TAN','artist',179.9),
(2, 1, 'NAT','teacher',222.9),
(2, 1, 'IVA','math',213.1),
(2, 1, 'NAT','sport',151.9),
(2, 1, 'IVA','math',134.1),
(3, 1, 'EVG','boss',142.0),
(3, 1, 'NAT','actor',167.9),
(3, 1, 'EVG','boss',333.0),
(4, 1, 'TAN','language',543.9),
(4, 1, 'IVA','math',654.1),
(4, 1, 'TAN','artist',465.9),
(4, 1, 'TAN','programmer',789.9);

How to get all sorts of unique pairs of names of employees of some department with different positions?

Answer 1

group by name, position order by name

Answer 2

If you mean those pairs that are not in the table, then this is through CROSS JOIN:

SELECT DISTINCT a.name, b.position
FROM (SELECT name FROM employee) AS a  CROSS JOIN (SELECT position FROM employee) AS b;

Employees of only a certain department, but positions from any department? In the first subquery, add a condition on DEPARTMENT_ID