T-SQL 查询根据多个表将行转换为列

Question

我有两个主表 CompanyMaster，一个子表 CompanyActivities

ActivityMaster ActivityMaster

ACTIVITYID   ACTIVITYNAME
A1           testActivity
A2           someActivity
A3           otheractivity
A4           someotheractivity
A5           anyotheractivity

CompanyMaster

COMPANYID  COMPANYNAME
C1         testcompany
C2         ACompany
C3         MyCompany
C4         SomeCompany
C5         ZCompany
C6         Company123
C7         ComapnyABC

CompanyActivities - CompanyActivities 中的 COMPANYID 与中的 COMPANYID 具有主键-外键关系CompanyMaster（主键表）和 ACTIVITYID 与 ActivityMaster 中的 ACTIVITYID 具有主键-外键关系（主键表） key 表）

COMPANYID   ACTIVITYID
C1          A1
C1          A3
C3          A1
C3          A2
C4          A5
C5          A1
C6          A3
C7          A3

我想编写一个查询来获取以下输出，其中 ActivityMaster 表的 ACTIVITYID 列中的所有行都将转换为列

输出

Companies  A1   A2  A3  A4  A5
C1         Y    N   Y   N   N
C2         N    N   N   N   N
C3         Y    Y   N   N   N
C4         N    N   N   N   Y
C5         Y    N   N   N   N
C6         N    N   Y   N   N
C7         N    N   Y   N   N

输出表显示所有公司作为第一列中的行和所有活动都显示为从第一列之后开始的列，如果有行同时包含 ACTIVITYID 和 COMPANYID ，它将设置为 Y 在输出中，否则它将被设置为 N

例如 - COMPANYID C1 有一个活动 ACTIVITYID A1 中CompanyActivities 表，因此位于 A1 下方且位于 C1 右侧的第二列中的第一行设置为 Y，而 C1 和 A2 没有一行，因此第一行中的第三列设置为 N

我正在使用 C# .net 和 4 个 for 循环来实现现在正在谈论的输出对应用程序的性能造成沉重负担，所以我想使用查询来执行此操作，我搜索了枢轴查询，但我发现的所有示例都事先知道列名称，但我不知道，我只得到通过查询ActivityMaster来获取列名。

Answer 1

create table #CompanyMaster (COMPANYID int, COMPANYNAME varchar(30))
create table #ActivityMaster (ACTIVITYID int, ACTIVITYNAME varchar(30))
create table #CompanyActivities (COMPANYID int, ACTIVITYID int)

insert into #CompanyMaster
    SELECT 1, 'Company A'
    union all
    SELECT 2, 'Company B'

insert into #ActivityMaster
    SELECT 101, 'Activity X'
    union all
    SELECT 102, 'Activity Y'    
    union all
    SELECT 103, 'Activity Z'    

insert into #CompanyActivities      
    select 1, 102
    union all
    select 2, 101

-- build activities column names
--case [Activity X] when 0 then ''N'' else ''Y'' end as [Activity X],
--case [Activity Y] when 0 then ''N'' else ''Y'' end as [Activity Y],
--case [Activity Z] when 0 then ''N'' else ''Y'' end as [Activity Z]
declare @activities nvarchar(max)
set @activities
   = (
    select 'case [' + ACTIVITYNAME + '] when 0 then ''N'' else ''Y'' end as [' + ACTIVITYNAME + '],' + char(10)
    from #ActivityMaster
    for xml path('')
   )
set @activities = substring(@activities, 0, len(@activities)-1)

declare @activities_for nvarchar(max)
-- build activities column names in for
--[Activity X], [Activity Y], [Activity Z]
set @activities_for
   = (
    select '[' + ACTIVITYNAME + '],' + char(10)
    from #ActivityMaster
    for xml path('')
   )
set @activities_for = substring(@activities_for, 0, len(@activities_for)-1)


declare @sql nvarchar(MAX) = N'
select COMPANYNAME,
    <activities>
From
    (select c.COMPANYNAME, a.ACTIVITYNAME,
        (case 
            when ca.ACTIVITYID is not null and ca.COMPANYID is not null then 1
            else 0
        end) as STATUS
    from #CompanyMaster c
    cross join #ActivityMaster a
    left join #CompanyActivities ca on ca.COMPANYID = c.COMPANYID and a.ACTIVITYID = ca.ACTIVITYID)  p
pivot
    (
        sum(STATUS) for ACTIVITYNAME IN (<activities_for>)
    ) as pvt
'


set @sql = replace(@sql, '<activities>', @activities)
set @sql = replace(@sql, '<activities_for>', @activities_for)

print @sql
exec sp_executesql @sql

drop table #CompanyMaster
drop table #ActivityMaster
drop table #CompanyActivities