sql server:sql動态行轉列(pivot) 例子

declare @sCompetitorName varchar(MAX)=''
select @sCompetitorName = @sCompetitorName + '[' + CompetitorName + '],' from Competitor
if(LEN(@sCompetitorName)>1) 
  set @sCompetitorName = LEFT(@sCompetitorName,len(@sCompetitorName)-1)
--PRINT @sCompetitorName

DECLARE @Headers VARCHAR(MAX)
SELECT @Headers =
  COALESCE(
    @Headers + ',isnull([' + cast(CompetitorName as varchar) + '],0) as [' + cast(CompetitorName as varchar) + ']',
    'isnull([' + cast(CompetitorName as varchar) + '],0) as [' + cast(CompetitorName as varchar) + ']'
  )
FROM Competitor
--PRINT @Headers

declare @sSQL VARCHAR(MAX)='
select b.CompetitorID,b.ID,' +
  @Headers +
  ' from
(
  select C.CompetitorID,C.CompetitorName,ISNULL(PCD.Discount,0) AS Discount,PCD.ID
  from Competitor C
  inner JOIN PitchCompetitorDiscount PCD ON PCD.CompetitorID=C.CompetitorID
  inner JOIN PitchDetail PD ON PD.ID=PCD.ID
) as a pivot
(
  MIN(Discount) FOR CompetitorName in ( ' + @sCompetitorName + ')
) as b
'
EXEC(@sSQL)