c# - 给定单个数据库表，是否可以在 sql 查询中生成报告矩阵作为结果集

Question

给定以下 SQL 表:

GroupAvailable, GroupAssigned, Title
----------------------------------------
GroupA,GroupA,Widget1
GroupA,GroupB,Widget1
GroupB,GroupA,Widget1

需要一个看起来像这样的报告:

Widget1
               GroupA     GroupB

GroupA           X          X
GroupB           X


Widget2
etc.

这可以在查询中进行吗？或者非常接近的东西？或者代码和 sql 的组合会更好吗？

Answer 1

PIVOT函数将使您接近结果:

select name, 
  coalesce(GroupA, '') GroupA, 
  coalesce(GroupB, '') GroupB
from
(
  select title +' '+ groupavailable name,
    groupassigned,
    'X' as flag
  from yourtable
) d
pivot
(
  max(flag)
  for groupassigned in (GroupA, GroupB)
) piv;

参见 SQL Fiddle with Demo .

如果您有未知数量的组，那么您可以使用动态 SQL 来获取结果:

DECLARE @cols AS NVARCHAR(MAX),
    @colsNull AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(GroupAvailable) 
                    from yourtable 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT distinct ', coalesce(' + QUOTENAME(GroupAvailable)+', '''') as '+QUOTENAME(GroupAvailable)
                    from yourtable 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT name,' + @colsNull + ' 
            from 
            (
                select title +'' ''+ groupavailable name,
                  groupassigned,
                  ''X'' as flag
                from yourtable
            ) x
            pivot 
            (
                max(flag)
                for groupassigned in (' + @cols + ')
            ) p '

execute(@query);

参见 SQL Fiddle with Demo

两个版本都会给出一个结果:

|           NAME | GROUPA | GROUPB |
------------------------------------
| Widget1 GroupA |      X |      X |
| Widget1 GroupB |      X |        |
| Widget2 GroupB |      X |        |