How do I pivot dynamically

Question

I have two tables; Leads, Territories and Referrers.

Lead has columns:

ID, Name, TerritoryId

Referrers has:

ID, LeadId, Name

Territory has:

ID and Name

A lead always relates to a territory and a lead can optionally relate to a Referrer.

Leads and Referrer records are regularly inserted (Referrers less frequently). I want to output a report in a GridView which looks like this:

Territory | Lead Count | Ref1 Lead Count | Ref2 Lead Count | Ref3 Lead Count

Leeds       10            1                7                 2

Exeter     43             9                21                8

etc...

OK, so the problem is, I want to group by Territory and Count the leads per territory.... this is fine:-

select t.Name, COUNT(1)
from Territory t inner join Lead l on l.TerritoryID = t.Id
group by t.Name

But now I want to break down count by referrer.

I understand I can do that partly with PIVOT, however, I understand that I have to explicitly state the Referrers in code. Is there any way to perform some kind of dynamic pivot which appends additional columns based on the number of rows in Referrer?

Would I have to use dynamic SQL inside an SP?

Answer 1

something like this:

select * from (select r.name, t.name as Territory  
from referrers r join Lead l on l.Id = r.leadId
join Territory  t on l. TerritoryID = t.Id) s 
pivot(count(Name) for Name in ([geoff],[fred])) p

As far as I can see the referrers would have to be specified explicitly, so you would have to generate the square bracketed list in an sp if you wanted them to be dynamic.