静态解决方案正在使用这种类型的查询：
SELECt ProjectID, SUM(CASE WHEN Date = '1/1/2010' THEN Hour ELSE 0 END) As '1/1/2010', SUM(CASE WHEN Date = '1/2/2010' THEN Hour ELSE 0 END) As '1/2/2010', SUM(CASE WHEN Date = '1/3/2010' THEN Hour ELSE 0 END) As '1/3/2010', SUM(CASE WHEN Date = '1/4/2010' THEN Hour ELSE 0 END) As '1/4/2010', ...FROM yourTableGROUP BY ProjectID;
为了使用动态解决方案，您需要使用动态SQL，如下所示：
Declare @SQL nvarchar(MAX)SELECt @SQL = ISNULL(@SQL, 'SELECT ProjectID') + ', SUM(CASE WHEN Date = ''' + [Date] + ''' THEN [Hour] ELSE 0 END) As [' + [Date] + ']'FROM @tGROUP BY [Date]SELECt @SQL = @SQL + ' FROM yourTable GROUP BY ProjectID;'EXEC(@SQL)
