Finding combinations

Database

Finding combinations

Hi,

I have table with below data. course_name is in ascending order.




emp_no course_name
1 a
1 b
1 c

Now, I want to get below combinations of the course_name with help of SQL.




emp_no course
1 a
1 b
1 c
1 ab
1 ac
1 bc

How can I get this?

1 REPLY
Senior Apprentice

Re: Finding combinations

You need a Cross Join:

SELECT t1.emp_no, 
t1.course_name
|| CASE
WHEN t1.course_name = t2.course_name
THEN ''
ELSE t2.course_name
END
FROM tab AS t1 JOIN tab AS t2
ON t1.emp_no = t2.emp_no
AND t1.course_name <= t2.course_name

But why do you want that output?

Dieter