Vision Binary

from http://stackoverflow.com/questions/14066927/what-are-some-good-ways-to-transpose-data-in-a-sql-table-from-row-columns-to-col

If you are using SQL Server 2005+, then you have a few options to transpose the data.

Existing table has following Columns: AID (nvarchar unique) ASID (nvarchar unique) Milestone (M1, M2, M3…M100) MilestoneDate (datetime)

Transposed data as follows: AID, ASID, M1 Date, M2 Date, M3 Date, M5 Date

You can implement the PIVOT function similar to this:

select AID, ASID, 
  M1 as M1_Date, M2 as M2_Date, 
  M3 as M3_Date, M4 as M4_Date, M5 as M5_Date
from(select AID, ASID, Milestone,
    MilestoneDate
  from yourtable
  where AID = whatever -- other filters here) src
pivot(
  max(milestonedate)for milestone in(M1, M2, M3, M4, M5...)) piv

Or you can use an aggregate function with a CASE statement:

select aid, 
  asid, 
  max(casewhen milestone ='M1'then milestonedate elsenullend) M1_Date,
  max(casewhen milestone ='M2'then milestonedate elsenullend) M2_Date,
  max(casewhen milestone ='M3'then milestonedate elsenullend) M3_Date,
  max(casewhen milestone ='M4'then milestonedate elsenullend) M4_Date
from yourtable
where AID = whatever -- other filters here groupby aid, asid

The above two queries work great if you have a known number of milestone values. But if not, then you can implement dynamic sql to transpose the data. The dynamic version would be similar to this:

DECLARE@cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX),@colNames AS NVARCHAR(MAX),select@cols = STUFF((SELECTdistinct','+ QUOTENAME(Milestone)from yourtable
            FORXML PATH(''), TYPE
            ).value('.','NVARCHAR(MAX)'),1,1,'')select@colNames = STUFF((SELECTdistinct','+ QUOTENAME(Milestone+'_Date')from yourtable
            FORXML PATH(''), TYPE
            ).value('.','NVARCHAR(MAX)'),1,1,'')set@query ='SELECT AID, ASID,'+@colNames +' from 
             (
                select AID, ASID, Milestone,
                  MilestoneDate
                from yourtable
                where AID = whatever -- other filters here 
            ) x
            pivot 
            (
                max(MilestoneDate)
                for Milestone in ('+@cols +')
            ) p 'execute(@query)