Eugene Elutin (3/19/2013)

---

Are you sure that OP wants one column with all subjects concatenated?

If not, then he might want dynamic cross-tab:

SELECT dow.[DAY], s.[Subject], ROW_NUMBER() OVER (PARTITION BY dow.[DAY] ORDER BY s.[Subject]) SN

INTO #ds

FROM [DAYOFWEEK] AS dow

JOIN SubjectToStudy AS s

ON s.WeekId = dow.WeekId

DECLARE @sql NVARCHAR(2000) = '';

SELECT @sql = @sql + '

,MAX(CASE WHEN SN = ' + CAST(SN AS VARCHAR) + ' THEN [Subject] ELSE '''' END) AS Subject_' + CAST(SN AS VARCHAR)

FROM (SELECT DISTINCT SN FROM #ds) q ORDER BY SN

SET @SQL = 'SELECT [Day] ' + @SQL + ' FROM #ds GROUP BY [Day] ORDER BY [Day] '

EXEC (@sql)

Brilliant stuff.. well done:cool: