-1

I want to run a sql query to get the rows of one table and the number of references to it from another table. However the number of references should be splitt, by a field in the second table.

Here is my simplified DB-Schema

TableA             TableB
------             ------
ID                 FK_A
                   GroupId

I want to query for TableA and count the referencing elements of TableB, but grouping them because of their GroupId. A result may look like this (Group1, Group2 and Group3 are the values inside TableB.GroupId):

SELECT ID, ??? FROM TableA

ID Group1 Group2 Group3
1  1      0      7
2  3      3      9

I should count all of the occurrences in TableB, that reference my TableA.

I don't really know how to make the subquery or whatever is needed here.

UPDATE I do not know, which GroupIds can be in TableB. So multiple count statements aren't working.

Cœur
  • 37,241
  • 25
  • 195
  • 267
abc
  • 2,285
  • 5
  • 29
  • 64

1 Answers1

0

Use Conditional Aggregate

select FK_A,
       Count(case when GroupId = 1 then 1 end) as Group1,
       Count(case when GroupId = 2 then 1 end) as Group2,
       Count(case when GroupId = 3 then 1 end) as Group3
From TableB
Group by FK_A

If the number of Groups are unknown then 

DECLARE @col VARCHAR(8000)= (SELECT ',' + Quotename(GroupId)
   FROM   TableB
   GROUP  BY GroupId
   FOR xml path (''))

SET @col = Stuff(@col, 1, 1, '')

DECLARE @sql VARCHAR(8000)='select * from TableB pivot (count(GroupId) for GroupId in ('
 + @col + ')) pv'

EXEC(@sql)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172