Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Help with the query! Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 12:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:58 AM
Points: 264, Visits: 655
Thanks Fellas for such brilliant answers. A lot of things to learn from all the answers ...
Post #1433015
Posted Wednesday, March 20, 2013 4:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:47 AM
Points: 11, Visits: 103
[quote]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
Post #1433117
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse