多列透视SQL服务器(Multi Column Pivot SQL Server)

我有一个与SQL Server 2008中多列回转交易，我想通过一个例子来说明问题。

下面是使用接合内和左外连接6代不同的表的结果

ID  Type    Date    Location    Result  Proc    ProcDate    ProcDetail        ProcNotes
--------------------------------------------------------------------------------------
1   ABC    1/1/2010   OK          AO    Proc_A  1/1/2013   This is Detail   Proc_A Notes
1   XYG    1/2/2011                     Proc_A  1/1/2013   This is Detail   Proc_A Notes
1   ABC    1/1/2010   OK          AO    Proc_B  1/1/2011   This is Detail   Proc_B Notes
1   XYG    1/2/2011                     Proc_B  1/1/2011   This is Detail   Proc_B Notes

下面是上表的细节

有两种类型的ABC和XYG和相应的日期

位置及其结果与ABC型联营

有2周PROC Proc_A和Proc_B和相应的日期

ProcDetail和ProcNotes与PROC Proc_A同伙

值都不被预知的

相反，上述结果集，我想有一个单列如下结果。

ID       Type1     Date1       Type2     Date2     Location   Result    Proc1     ProcDate1    ProcNotes1        ProcDetail           Proc2       ProcDate2      ProcNotes2
 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1        ABC     1/1/2010      XYZ     1/2/2011    OK         AO       Proc_A     1/1/2013    Proc_A Notes       This is Detail       Proc _B      1/1/2011         Proc_B Notes

感谢您寻找到它。

如果值ABC和XYG已知前期你可以做有条件的聚集

SELECT ID,
       MAX(CASE WHEN type = 'ABC' THEN 'ABC' END) Type1,
       MAX(CASE WHEN type = 'ABC' THEN Date END) Date1,
       MAX(CASE WHEN type = 'XYG' THEN 'XYZ' END) Type2,
       MAX(CASE WHEN type = 'XYG' THEN Date END) Date2,
       MAX(Location) Location,
       MAX(Result) Result,
       MAX(CASE WHEN type = 'ABC' THEN [Proc] END) Proc1,
       MAX(CASE WHEN type = 'ABC' THEN ProcDate END) ProcDate1,
       MAX(CASE WHEN type = 'ABC' THEN ProcDetail END) ProcDetail1,
       MAX(CASE WHEN type = 'ABC' THEN ProcNotes END) ProcNotes1,
       MAX(CASE WHEN type = 'XYG' THEN [Proc] END) Proc2,
       MAX(CASE WHEN type = 'XYG' THEN ProcDate END) ProcDate2,
       MAX(CASE WHEN type = 'XYG' THEN ProcDetail END) ProcDetail2,
       MAX(CASE WHEN type = 'XYG' THEN ProcNotes END) ProcNotes2
  FROM
(
  SELECT * FROM table1 -- that's to emulate your current query with multiple joins
) q
 GROUP BY ID

输出示例：

| ID | TYPE1 |                          DATE1 | TYPE2 |                          DATE2 | LOCATION | RESULT |  PROC1 |                      PROCDATE1 |     PROCDETAIL1 |   PROCNOTES1 |  PROC2 |                      PROCDATE2 |     PROCDETAIL2 |   PROCNOTES2 |
|----|-------|--------------------------------|-------|--------------------------------|----------|--------|--------|--------------------------------|-----------------|--------------|--------|--------------------------------|-----------------|--------------|
|  1 |   ABC | January, 01 2010 00:00:00+0000 |   XYZ | January, 02 2011 00:00:00+0000 |       OK |     AO | Proc_B | January, 01 2013 00:00:00+0000 | This is Details | Proc_B Notes | Proc_B | January, 01 2013 00:00:00+0000 | This is Details | Proc_B Notes |

这里是SQLFiddle演示