I have this table and data:

CREATE TABLE #t1
(
  [Year]      int,
  Student     int,
  Test        char(2),
  Score       int,
  Achievement varchar(32)
);

INSERT #t1 VALUES
(2014,1,'MA',697, 'Proficient'),
(2013,1,'CA',641, 'Basic'),
(2015,1,'MA',2447,'Below Basic'),
(2017,1,'MA',464, 'Proficient'),
(2012,1,'CA',611, 'Below Basic'),
(2014,1,'MA',749, 'Advanced'),
(2016,1,'CA',459, 'Below Basic'),
(2013,1,'MA',779, 'Advanced');

Now I want to create a SQL query from #t1 in which the result should be like this:

<img src="https://i.stack.imgur.com/aaLZo.png" width="450">

New contributor
il.king is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

put on hold as unclear what you're asking by Md Haidar Ali Khan, Tom V, mustaccio, Philᵀᴹ, LowlyDBA Nov 9 at 17:21

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    The answer to the subject of your question is pivot but what you are doing is essentially an execel or ssrs like display... so handle it in the presentation layer. – scsimon Nov 8 at 17:39
  • 2
    Hello, please show tables, queries and all other data in a textual way and avoid screenshots. Keep in mind that not everyone has access to images on imgur.com. Often these kind of sites are blocked by corporate firewalls. – luc Nov 8 at 17:50
up vote 2 down vote accepted

Basically you need to create a matrix of test/year combos. For each test, you need to "pivot" both the score and achievement for each year (but we're not using PIVOT here, because you're not pivoting a single aggregation). I use dynamic SQL for this. Presumably you are passing in a single Student at a time?

DECLARE @StudentID int = 1;  -- input parameter to a stored procedure

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
UNION ALL SELECT Test'
    + REPLACE((SELECT ', Score' + y + ' = MAX(CASE WHEN [Year] = ' 
    + y + ' AND Test=''$t$'' THEN Score END),
      Achivement' + y + ' = MAX(CASE WHEN [Year] = ' 
    + y + ' AND Test=''$t$'' THEN Achievement END)'
  FROM (SELECT RTRIM([Year]) AS y FROM #t1 GROUP BY [Year]) y
  ORDER BY y FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)'),'$t$',t.Test)
  + N' FROM #t1 WHERE Test = ''' + t.Test + ''' AND Student = @StudentID GROUP BY Test'
FROM #t1 AS t GROUP BY t.Test;

SET @sql = STUFF(@sql, 1, 12, '');

EXEC sys.sp_executesql @sql, N'@StudentID int', @StudentID;

Results (yes, I incorrectly spelled it Achivement):

enter image description here

This gets a little closer to your screen shot:

DECLARE @StudentID int = 1; -- input param, hopefully for a stored procedure

DECLARE @sql nvarchar(max) = N'SELECT [x]=''''';

SET @sql += (SELECT ',[x]=''' + RTRIM([Year]) + ''',[x]='''''
  FROM #t1 GROUP BY [Year]
  FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)');

SET @sql += '
UNION ALL SELECT ''''' + (SELECT ',''Score'',''Achievement'''
  FROM #t1 GROUP BY [Year]
  FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)');

SELECT @sql += N'
UNION ALL SELECT Test'
  + REPLACE((SELECT ', Score' + y + ' = MAX(CASE WHEN [Year] = ' 
  + y + ' AND Test=''$t$'' THEN RTRIM(Score) END),
    Achievement' + y + ' = MAX(CASE WHEN [Year] = ' 
  + y + ' AND Test=''$t$'' THEN Achievement END)'
      FROM (SELECT DISTINCT RTRIM([Year]) AS y FROM #t1) y
      ORDER BY y FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)'),'$t$',t.Test)
      + N' FROM #t1 WHERE Test = ''' + t.Test + ''' AND Student = @StudentID GROUP BY Test'
    FROM #t1 AS t GROUP BY t.Test;

EXEC sys.sp_executesql @sql, N'@StudentID int', @StudentID;

Results:

enter image description here

If you want to display the year on top of both columns, you'll have to figure that out at the presentation layer. T-SQL doesn't have a concept of colspan. This will always be ugly in T-SQL because it isn't a presentation language - you should figure out how to flip these rowsets sideways in whatever front end language is being used.

Not the answer you're looking for? Browse other questions tagged or ask your own question.