用于将行转置为列的 SQL 查询

Question

我有一个使用 SQL Server 2008 的 .net Web 应用程序。我尝试在网格中显示的数据表包含实际上是另一个表的行的列。现在，我正在 BLL 中执行此操作，将数据读取到数据表中;从另一个表中获取数据并将其转换为第一个数据表的列，然后遍历该数据表中的每一行数据以填充新列。非常耗时且缓慢。

我相信这可以通过SQL 2012及更高版本中使用"转置"或类似内容的查询来完成，但不确定在2008年是否可以。我研究并尝试使用"透视"，但我不擅长SQL，无法让它工作。

这是数据库表的简化示例，以及我需要显示的内容：

Facility Table:
FacilityID
12345
67890
PartnerInfo table:
PartnerID    Partner
1            Partner1
2            Partner2
3            Partner3
FacilityPartner table:
FacilityID    PartnerID
12345         1
12345         3
67890         2
67890         3

需要一个查询来返回类似以下内容：

FacilityID    Partner1    Partner2    Partner3
12345         true        false       true
67890         false       true        true

Answer 1

下面应该给出一些关于透视数据的想法。它并没有像你问的那样给你确切的真假。

declare  @facility table (facilityId int)
declare  @PartnerInfo  table (partnerid int, partnerN varchar(1000))
declare  @FacilityPartner table (facilityId int,partnerid int)
insert into @facility values (12345)
insert into @facility values (67890)
insert into @facility values (67891)
insert into @PartnerInfo values (1, 'partner1')
insert into @PartnerInfo values (2, 'partner2')
insert into @PartnerInfo values (3, 'partner3')
insert into @FacilityPartner values(12345, 1)
insert into @FacilityPartner values(12345, 3)
insert into @FacilityPartner values(67890, 2)
insert into @FacilityPartner values(67890, 3)
select f.facilityId as facid, p.PartnerN as partn, 100 as val
FROM @facility f
LEFT join @FacilityPartner fp on f.facilityId = fp.facilityId
LEFT JOIN @PartnerInfo p on p.partnerid = fp.partnerid
select facid, Partner1 , partner2,partner3 FROM 
(select f.facilityId as facid, p.PartnerN as partn, 100 as val
FROM @facility f
LEFT join @FacilityPartner fp on f.facilityId = fp.facilityId
LEFT JOIN @PartnerInfo p on p.partnerid = fp.partnerid) x
PIVOT(
avg(val)
for partn in ([partner1], [partner2],[partner3])
) as pvt

Answer 2

首先要理解的是，就像许多其他语言一样，SQL具有一种"编译"过程，在其中生成执行计划。SQL 查询必须能够在编译时知道列的精确数量和类型，而无需引用数据(它确实有一些可用于编译的表元数据，这就是SELECT *工作的原因)。

这意味着只有满足以下两个条件之一，才能执行要执行的操作：

1. 您必须提前知道合作伙伴的确切数量(在本例中为列的名称)。即使对于使用 PIVOT 关键字的查询也是如此。
2. 您必须愿意使用动态 SQL 分多个步骤执行此操作，其中第一步查看数据以了解您需要多少列。然后，您可以在 varchar 变量中构建一个新查询，最后使用Exec()或sp_executesql()执行该字符串。这是有效的，因为最后一步为该字符串变量调用了新的"编译"进程和执行上下文。

当然，还有第三种选择：透视客户端代码中的数据。这是我的偏好。但是，大多数人选择选项2。