使用分组计算Sql行数

Question

你好，我有两张桌子，如下所示

tblContactType

typeId  typeName active
1       Email      1
2       Phone      1
3       Address    1
4       Fax        1

tblContact

id    IdName   typeId  groupId
100    test      1       1
101    test2     1       1
102    test3     1       2
103    test4     2       2
104    test5     2       3
105    test6     3       3

希望结果以列名作为typeName计数，并按组id分组。结果应该是与组关联的类型总数，这些类型与联系人关联。

GroupId    EmailCount    PhoneCount  AddressCount     FaxCount
1           2             0             0               0
2           1             1             0               0
3           0             1             1               0  

Answer 1

您可以按以下方式分组和透视：

Select * from (
    Select t.groupid, tct.typename, t.id from tblContact t 
    inner join tblContactType tct 
    on t.typeid = tct.typeid
) a
pivot (count(a.id) for typename in ([Email],[Phone],[Address],[Fax]) ) p

对于列的动态列表，可以使用动态查询，如下所示：

declare @cols1 varchar(max)
declare @query nvarchar(max)

Select  @cols1 = stuff((Select distinct ','+QuoteName(typename) from tblContactType for xml path('')),1,1,'')
Set     @query = '  Select * from (
        Select t.groupid, tct.typename, t.id from tblContact t 
        inner join tblContactType tct 
        on t.typeid = tct.typeid
    ) a
    pivot (count(a.id) for typename in (' + @cols1 + ') ) p '

Select @query --Check the generated query is good and then execute below
--exec sp_executesql @query

输出如下：

+---------+---------+-------+-----+-------+
| groupid | Address | Email | Fax | Phone |
+---------+---------+-------+-----+-------+
|       1 |       0 |     2 |   0 |     0 |
|       2 |       0 |     1 |   0 |     1 |
|       3 |       1 |     0 |   0 |     1 |
+---------+---------+-------+-----+-------+

Answer 2

这是另一个解决方案。

SELECT groupId,
        SUM(CASE WHEN c.typeId = 1 THEN 1 ELSE 0 END) 'EmailCount',
        SUM(CASE WHEN c.typeId = 2 THEN 1 ELSE 0 END) 'PhoneCount',
        SUM(CASE WHEN c.typeId = 3 THEN 1 ELSE 0 END) 'AddressCount',
        SUM(CASE WHEN c.typeId = 4 THEN 1 ELSE 0 END) 'FaxCount'
FROM tblContact c
    JOIN tblContactType ct ON c.typeId = ct.typeId  
GROUP BY groupId 

结果

-------------------------------------------------------------
groupId  |  EmailCount | PhoneCount | AddressCount | FaxCount
-------------------------------------------------------------
  1      |     2       |    0       |      0       |    0
  2      |     1       |    1       |      0       |    0
  3      |     0       |    1       |      1       |    0
-------------------------------------------------------------