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

Answer 1

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.

Answer 2

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