SQL查询将行转置为列 [英] SQL Query to transpose rows into columns

问题描述

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

I have a .net web application that uses SQL Server 2008. The data table I am trying to display in a grid contains columns that are actually rows of another table. Right now, I am doing this in the BLL, reading data into data table; getting the data from another table and making it into columns of first data table and then going through each row of data in that data table to populate the new columns. Very time consuming and slow.

我相信可以通过使用"Transpose"或类似的方式在SQL 2012及更高版本中进行查询来完成此操作，但不确定在2008年是否可行.我研究并尝试使用"pivot"，但我并不擅长SQL和无法正常工作.

I believe this can be done through a query in SQL 2012 and above using "Transpose" or something similar but not sure if it is possible in 2008. I researched and tried using "pivot" but I am not good at SQL and couldn't get it to work.

这是数据库表以及我需要显示的简化示例:

This is a simplified example of DB tables and what I need to display:

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

需要查询以返回以下内容:

Need a query to return something like:

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

推荐答案

下面应该提供一些透视数据的想法.它并没有像您所要求的那样为您提供完全正确的错误.

Following should give some idea on pivoting the data. It doesn't give you exact true false as you asked.

    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

这篇关于SQL查询将行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！