Multi Column Pivot Sql Server

I have a problem that has deals with multi column pivoting in SQL Server 2008. I would like to explain by an example. Below is the result of joining 6 different tables using inner

Solution 1:

If values ABC and XYG are known upfront you can do conditional aggregation

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

Sample output:

| 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 |

Here is SQLFiddle demo