Pivot on fields with count of entries

Question

I have the following table that needs to be converted into a pivot both fields contain the same entries and a count of the entries need to be represented in a pivot table. The count of the entries has to done as groupings by entry types such as A or B.

Table

   X   Y
-----------
   A   B    
   A   C    
   D   B    
   B   C    
   C   C    
   D   E    
   D   B    
   F   C

Pivot

         A Or B   C Or D   E Or F
A Or B     1        2        0
C Or D     2        1        1
E Or F     0        1        0

possible duplicate of [How to create a PivotTable in Transact/SQL?](http://stackoverflow.com/questions/11617713/how-to-create-a-pivottable-in-transact-sql) — Taher Rahgooy, Aug 18 '15 at 15:31

score 0 · Answer 1 · edited Jan 22 '16 at 15:18

You will have to build a dynamic query if your groups are dynamic.

declare @t table(X char(1), Y char(1))
insert into @t values
('A', 'B')
, ('A', 'C')
, ('D', 'B')
, ('B', 'C')
, ('C', 'C')
, ('D', 'E')
, ('D', 'B')
, ('F', 'C')

Select [\] = G2, isnull([A Or B], 0), isnull([C Or D], 0), isnull([E Or F], 0) 
From (
    Select G1 = g1.name, G2 = g2.name, total = count(*) From @t t
    Inner Join (values('A Or B', 'A', 'B'), ('C Or D', 'C', 'D'), ('E Or F', 'E', 'F')) g1(name, X, Y) on t.X = g1.X or t.X = g1.Y
    Inner Join (values('A Or B', 'A', 'B'), ('C Or D', 'C', 'D'), ('E Or F', 'E', 'F')) g2(name, X, Y) on t.Y = g2.X or t.Y = g2.Y
    Group By g1.name, g2.name
) as d
Pivot (
    max(total)
    For G1 In ([A Or B], [C Or D], [E Or F])
) as piv

Pivot on fields with count of entries

1 Answers1