Search code examples
sqlsql-servert-sqlpivotunpivot

Transpose rows into columns in SQL without using aggregate functions like MAX, AVG etc

I have a table like this:

CREATE TABLE MyDataTable
(
     [RollNo] varchar(8), 
     [QuesBlock] int, 
     [RespVal] varchar(2)
);

INSERT INTO MyDataTable ([RollNo], [QuesBlock], [RespVal])
VALUES ('MBA0001', 1, A), ('MBA0001', 2, B), ('MBA0001', 3, D),
       ('MBA0002', 1, C), ('MBA0002', 2, A), ('MBA0002', 3, B),
       ('MBA0003', 1, B), ('MBA0003', 2, C), ('MBA0003', 3, A);

Therefore, my source data looks like this:

Source Data

Now I want to reach a target table structure like this:

enter image description here

This is basically to tabulate the candidate-wise, question-wise responses for each question in an OMR-based test for a school. I have a fixed number of unique values (only 50) in the QuesBlock column, so I can live with hardcoding. I have gone through examples where pivot has been used to achieve something like this, but all of them have used aggregate functions like MAX, MIN, AVG etc. for working with numeric values. But in my case, the values of the RESPVAL column are all textual. How do I achieve this?

Solution

  • You can use max() with characters/string. A simple old style pivot will work for this:

    select
    RollNo
  , Q1 = max(case when QuesBlock = 1 then RespVal else null end)
  , Q2 = max(case when QuesBlock = 2 then RespVal else null end)
  , Q3 = max(case when QuesBlock = 3 then RespVal else null end)
from MyDataTable
group by RollNo;

    or with pivot() like so:

    select
    RollNo
  , Q1
  , Q2
  , Q3
from (select RollNo, QuesBlock='Q'+convert(varchar(2),QuesBlock), RespVal
      from MyDataTable) as i
 pivot (max(RespVal) for QuesBlock in (Q1,Q2,Q3)) as p;

    or dynamically pivot() like so:

    declare @query nvarchar(max);
declare @cols nvarchar(max);

select @cols = stuff((select ','+quotename('Q'+convert(varchar(2),QuesBlock))
         from MyDataTable as C
         group by c.QuesBlock
         order by c.QuesBlock
         for xml path('')), 1, 1, '');
set @query = 'select RollNo, '+@cols+'
      from(select RollNo, QuesBlock=''Q''+convert(varchar(2),QuesBlock), RespVal
        from MyDataTable) as i
      pivot
      (
        max(RespVal)
        for QuesBlock in ('+@cols+')
      ) p';
exec sp_executesql @query;

    test setup: http://rextester.com/TURW69000

    all three return:

    +---------+----+----+----+
| RollNo  | Q1 | Q2 | Q3 |
+---------+----+----+----+
| mba0001 | A  | B  | D  |
| mba0002 | C  | A  | B  |
| mba0003 | B  | C  | A  |
+---------+----+----+----+