简单枢轴表

Question

我有两个表:接口和ConnectionStrings。这些表之间有很多到很多的关系，我通过添加第三个表InterfaceConnectionStrings来规范这个关系。我可以通过存储过程查询表，以获得我需要的数据：

Select I.InterfaceName as IName
,CS.ConnectionStringDescription as Descr
,CS.ConnectionStringValue as Value
from ConnectionStrings CS
JOIN InterfaceConnectionStrings ICS
on CS.ConnectionStringDescription = ICS.ConnectionStringDescription
JOIN Interfaces I
on ICS.InterfaceName = I.InterfaceName
where InterfaceName = @InterfaceName

结果如下所示：

**IName**       **Descr**     **Value**
InterfaceX      CSDescr1      CS1Value
InterfaceX      CSDescr2      CS2Value
InterfaceX      CSDescr3      CS3Value

我想找到一个查询，它将返回以下内容：

**IName**       **CSDescr1**     **CSDescr2**  **CSDescr3**
InterfaceX      CS1Value         CS2Value      CS3Value   

我试着使用枢轴，但没有取得多大进展。以下是我到目前为止所拥有的：

Select InterfaceName as IName
,ConnectionStringDescription as Descr
,ConnectionStringValue as Value
From (
Select I.InterfaceName 
,CS.ConnectionStringDescription
,CS.ConnectionStringValue
 from ConnectionStrings CS
JOIN InterfaceConnectionStrings ICS
on CS.ConnectionStringDescription = ICS.ConnectionStringDescription
JOIN Interfaces I
on ICS.InterfaceName = I.InterfaceName) PvtBase

PIVOT

( MAX(PvtBase.Value) FOR
PvtBase.Descr IN PvtBase.Value) as Pvt

Answer 1

例如：

declare @table table (iName varchar(25), Descr varchar(25), Value varchar(25))

insert into @table
select 'InterfaceX','CSDescr1','CS1Value' UNION ALL
select 'InterfaceX','CSDescr2','CS2Value' UNION ALL
select 'InterfaceX','CSDescr3','CS3Value'

select *
from (select * from @table) sub
pivot (max(value) for descr in ([CSDescr1],[CSDescr2],[CSDescr3])) p

对于您来说，只需将SELECT * FROM @TABLE替换为初始查询(添加一些别名)。

select *
from ( Select I.InterfaceName as iName
             ,CS.ConnectionStringDescription as descr
             ,CS.ConnectionStringValue as value
             from ConnectionStrings CS 
             JOIN InterfaceConnectionStrings ICS
                on CS.ConnectionStringDescription = ICS.ConnectionStringDescription
             JOIN Interfaces I
                on ICS.InterfaceName = I.InterfaceName
             where InterfaceName = @InterfaceName) sub
pivot (max(value) for descr in ([CSDescr1],[CSDescr2],[CSDescr3])) p

Answer 2

SELECT *
FROM (
        Select  I.InterfaceName                 as IName
               ,CS.ConnectionStringDescription  as Descr
               ,CS.ConnectionStringValue        as Value
        from ConnectionStrings CS
        JOIN InterfaceConnectionStrings ICS
        on CS.ConnectionStringDescription = ICS.ConnectionStringDescription
        JOIN Interfaces I
        on ICS.InterfaceName = I.InterfaceName
        where InterfaceName = @InterfaceName
     ) t
PIVOT (MAX(Value)
       FOR Descr
       IN ([CSDescr1],[CSDescr2],[CSDescr3])
      )p