Pivot sample with dynamic columns

Question

I've seen many pivot examples but I didn't find any that applies to my case. I hope someone can give me a hand.

I have the these two tables

Entities Performance    
+----------+--------+----------+
| idEntiry | idWeek | idResult |
+----------+--------+----------+
| 1        | 1      | 1        |
| 2        | 1      | 1        |
| 3        | 1      | 2        |
| 1        | 2      | 3        |
| 2        | 2      | 1        |
| 3        | 2      | 2        |
| 1        | 3      | 3        |
| 2        | 3      | 1        |
| …        | …      | …        |
| 1        | 10     | 1        |
+----------+--------+----------+

Number of weeks is dynamic
Number of entities is also dynamic

Performance details     
+----------+-------------+--------+
| idResult | Description | Color  |
+----------+-------------+--------+
|        1 | Bad         | Red    |
|        2 | Average     | Yellow |
|        3 | Good        | Green  |
+----------+-------------+--------+

And this is the desired output

           |               Weeks                |
+----------+---------+---------+------+---+-----+
| idEntity | 1       | 2       | 3    | … |  10 |
+----------+---------+---------+------+---+-----+
|        1 | Bad     | Good    | Good | … | Bad |
|        2 | Bad     | Bad     | Bad  | … |     |
|        3 | Average | Average |      | … |     |
+----------+---------+---------+------+---+-----+

Thanks!

Answer 1

This might help you get close.

DECLARE @WeekStart INT,
        @WeekEnd INT

SELECT  @WeekStart = MIN(idWeek),
        @WeekEnd = MAX(idWeek)
FROM    dbo.WeeksTable

DECLARE @WeekColumns VARCHAR(MAX)

WHILE @WeekStart < @WeekEnd
BEGIN
    SET @WeekColumns = COALESCE(@WeekColumns + '],[', '[') + CONVERT(VARCHAR, @WeekStart)
    SET @WeekStart = @WeekStart + 1
END
SET @WeekColumns = CONCAT(@WeekColumns,'],[',CONVERT(VARCHAR, @WeekStart),']')

DECLARE @Sql VARCHAR(MAX) = '
SELECT idEntity, ' + @WeekColumns + ' FROM
(
SELECT  ep.idEntity,
        ep.idWeek,
        pd.Description
FROM    dbo.EntitiesPerformance ep
        JOIN dbo.PerformanceDetails pd on ep.idResult = pd.idResult
) src
PIVOT (
    MIN(Description)
    FOR idWeek IN (' + @WeekColumns + ')
) pvt
'
EXEC (@Sql)