SQL Intersection with group by a column values

Question

I want to intersect between values of one column based on another column. I mean if I have three different values in Id column, I want the values of LineGroupBy column to be assumed as one group(Id=1) and intersected with other groups(Id=5 or 8) Here is the table:

LineGroupBy    Id

ItemTypeId      8
ItemId          1
BankAccountId   5
ItemTypeId      1
VATId           1
InOut           5
FundTypeId      5
CurrencyId      5
VATPer          1
ItemId          8

the result of query should be NULL. As you can see the LineGroupBy values of Id=1 is (ItemId,ItemTypeId,VATId,VATPer), Id=5 is (InOut,FundTypeId,CurrencyId) and Id=8 is (ItemTypeId,ItemId).

So Id=1 and Id=8 has an intersect of (ItemTypeId,ItemId) but overall there is no intersect between all Ids.

I tried this but it is static(address exact Ids) and I want a query without addressing Ids.

SELECT *
FROM #temp T
WHERE T.Id=1
INTERSECT
SELECT *
FROM #temp T
WHERE T.Id=5
INTERSECT
SELECT *
FROM #temp T
WHERE T.Id=8

The result should be null because these three groups dont have any common item. — Vajihe Jami, May 14 '23 at 07:25
"I want a dynamic query without addressing ids". Please explain what you mean by this. I presume you are not talking about dynamic SQL??? — Jonathan Willcock, May 14 '23 at 07:44
It would be far easier if you included in your question a case where the result is not null! — Jonathan Willcock, May 14 '23 at 07:45
you are right. I am not talking about dynamic query. I mean without addressing the Ids. I will edit the question to make it easier to understand what I mean. — Vajihe Jami, May 14 '23 at 07:52

score 0 · Answer 1 · answered May 14 '23 at 07:49

I find this code to state the overlaps but this code does not say what is the intersect of all groups.

WITH temp(Id) AS
( 
SELECT DISTINCT Id
FROM #temp
), 
couples (Id1, Id2, LineGroupBy) AS 
( 
SELECT a.Id, b.Id, a.LineGroupBy
    FROM #temp a 
    JOIN #temp b ON a.Id < b.Id AND a.LineGroupBy = b.LineGroupBy 
    GROUP BY a.Id, b.Id, a.LineGroupBy
)
SELECT a.Id AS Id1, 
       b.Id AS Id2, 
       c.LineGroupBy AS common_items
FROM temp a
INNER JOIN temp b ON a.Id <= b.Id
LEFT JOIN couples c ON  a.Id = c.Id1 AND b.Id = c.Id2

An extra code to find a route of all Ids could work.

Paul Maxwell · Answer 2 · 2023-05-15T03:35:30.223

This uses string concatenations to determine which ids share LineGroupBy values. It also ensures the ids aren't repeated and are ordered.

SELECT Groups.LineGroupBy, STUFF((
  SELECT ',' + CAST(t2.id AS VARCHAR(20))
  FROM (select distinct LineGroupBy, Id from temp) t2
  WHERE Groups.LineGroupBy = t2.LineGroupBy
  ORDER BY t2.id
  FOR XML PATH('')
), 1, 1, '') AS IdList
FROM (SELECT DISTINCT LineGroupBy FROM temp) AS Groups

LineGroupBy	IdList
BankAccountId	1,5,6,8 << test data: this is common to all ids
CurrencyId	5,6
FundTypeId	5,6
InOut	5,6
ItemId	1,6,8
ItemTypeId	1,6,8
VATId	1,6
VATPer	1,6

Then we add a comparison of that result (above) to the distinct list of ids, so that the where clause is also automated:

WITH CTE as (
    SELECT Groups.LineGroupBy, STUFF(
        (
          SELECT ',' + CAST(t2.id AS VARCHAR(20))
          FROM (SELECT DISTINCT LineGroupBy, Id FROM temp) t2
          WHERE Groups.LineGroupBy = t2.LineGroupBy
          ORDER BY t2.id
          FOR XML PATH('')
        ), 1, 1, '') AS IdList
    FROM (SELECT DISTINCT LineGroupBy FROM temp) AS Groups
  )
SELECT LineGroupBy, IdList
FROM CTE
WHERE IdList = (
    SELECT STUFF((
        SELECT ',' + CAST(id AS VARCHAR(20))
        FROM (SELECT DISTINCT Id FROM temp) t
        ORDER BY id
    FOR XML PATH('')
    ), 1, 1, '') 
  )

LineGroupBy	IdList
BankAccountId	1,5,6,8

fiddle

Original post:

I'm unsure what you are really asking for as you have not specified any actual output, but thought perhaps this may help:

SELECT Groups.LineGroupBy, STUFF((
    SELECT ',' + CAST(t2.id AS VARCHAR(20))
    FROM temp t2
    WHERE Groups.LineGroupBy = t2.LineGroupBy
    FOR XML PATH('')
), 1, 1, '') AS IdList
FROM (SELECT DISTINCT LineGroupBy FROM temp) AS Groups

From the given sample data:

LineGroupBy	IdList
BankAccountId	5
CurrencyId	5
FundTypeId	5
InOut	5
ItemId	1,8
ItemTypeId	8,1
VATId	1
VATPer	1

INSERT INTO temp (LineGroupBy, Id)
VALUES
    ('ItemTypeId',6),
    ('ItemId',6),
    ('BankAccountId',6),
    ('ItemTypeId',6),
    ('VATId',6),
    ('InOut',6),
    ('FundTypeId',6),
    ('CurrencyId',6),
    ('VATPer',6)
  ;

after adding some extra data

LineGroupBy	IdList
BankAccountId	5,6
CurrencyId	5,6
FundTypeId	5,6
InOut	5,6
ItemId	1,8,6
ItemTypeId	8,1,6,6
VATId	1,6
VATPer	1,6

fiddle

That works. Just need a where clause to filter items that are present in all groups. — Vajihe Jami, May 14 '23 at 09:30
Excellent. I thought it might be something like this. So is this sufficient as your solution or do you need more? — Paul Maxwell, May 14 '23 at 12:28

SQL Intersection with group by a column values

2 Answers2