SQL joining two columns on single column to get count

Question

I am strugling to figure out how to receive what I need with t-sql.
My goal is to count distinct operations, which contains certain item code combinations in side of them.
Final desired output table:

SELECT '1' as code_1, '2' as code_2, 2 as op_count INTO #final_table_example UNION
SELECT '2' as code_1, '3' as code_2, 0 as op_count 

I have two tables:

Table A is like the output/goal table, but without [op_count] (this is the column I want to calculate).
Table B looks like this:

SELECT 'A' as operation, '1' as item_code INTO #operations UNION
SELECT 'A' as operation, '2' as item_code UNION
SELECT 'A' as operation, '3' as item_code UNION
SELECT 'B' as operation, '1' as item_code UNION
SELECT 'B' as operation, '3' as item_code

As you can see my Table B has 2 operations that have 1 & 3 item codes in them.
And only 1 operation have 2 & 3 code combination.
I need efficient query as I will scan few million operations.
Many thanks.

Answer 1

Here are 2 queries which show the information that you are looking for.

• The first query is the format requested. It looks for all combinations of 2 codes. If you have operations with many codes the number of rows returned will grow exponentially.
• The second query will group all the item_codes associated with an operation and then count the number of operation with each combination of codes.

NB When we have a query using UNION we only need to give column aliases for the first query as all other aliases will be ignored.

SELECT 'A' as operation, '1' as item_code INTO #operations UNION
SELECT 'A' as operation, '2' as item_code UNION
SELECT 'A' as operation, '3' as item_code UNION
SELECT 'B' as operation, '1' as item_code UNION
SELECT 'B' as operation, '3' as item_code UNION
SELECT 'C', '1' UNION SELECT 'C','2';

select 
  a.item_code code_1,
  b.item_code code_2,
  count(*) op_count
from #operations a
join #operations b on a.item_code < b.item_code
  and a.operation = b.operation
group by
  a.item_code,
  b.item_code;

code_1 | code_2 | op_count
:----- | :----- | -------:
1      | 2      |        2
1      | 3      |        2
2      | 3      |        1

;WITH cte AS 
(
SELECT
  STRING_AGG(item_code, ',') WITHIN GROUP (ORDER BY item_code) AS codes,
  operation
FROM #operations 
GROUP BY operation
)
SELECT
  codes,
  COUNT(operation) op_count
FROM cte
GROUP BY codes;

codes | op_count
:---- | -------:
1,2   |        1
1,2,3 |        1
1,3   |        1

db<>fiddle here