I have a table in SQL Server with the following structure:
DateRated, Parameter, Rating, UserID
Sample Data
DateRated Parameter Rating UserID
10/01/2010 Flexibility Good User101
10/01/2010 Efficiency Average User101
10/01/2010 Value For Money Below Average User101
I want to create a query that will make it like this:
DateRated Flexibility Efficiency Value For Money
10/01/2010 Good Average Below Average
I have managed it to make it up to this point:
DateRated Flexibility Efficiency Value For Money
10/01/2010 Good
10/01/2010 Average
10/01/2010 Below Average
Using this code:
DECLARE @t2 TABLE (ID int IDENTITY(1,1), Column2 nvarchar(255))
INSERT @t2 (Column2)(SELECT DISTINCT Parameter FROM Parameter_Master)
DECLARE @Max int
DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT '
DECLARE @Column nvarchar(255)
DECLARE @i int
SET @i = 1
SELECT @Max =MAX(ID) FROM @t2
WHILE @i <= @Max
BEGIN
SET @Column = (SELECT Column2 FROM @t2 WHERE ID = @i)
SET @SQL = @SQL + 'CASE WHEN Parameter = ''' + @Column + ''' THEN Rating END AS [' + @Column + ']' + CHAR(13)
SET @i = @i + 1
IF @i <= @Max
SET @SQL = @SQL + ','
END
SET @SQL = @SQL + ', Cast(Convert(varchar(8),RatedOn, 112) as Datetime), FROM Company_Ratings
where Company_ID = 2002 Group By RatedON, Rating, Parameter, '
EXEC (@SQL)I can't find a way to keep it in one line only. Any help will be much appreciated.
