Variation of pivot in SQL

Question

I have a table similar to:

+------+----------+----------+----------+----------+
| Name | Subject1 | Subject2 | Subject3 | Subject4 |
+------+----------+----------+----------+----------+
| x    |       10 |       11 |       12 |       13 |
| y    |       20 |       21 |       22 |       23 |
| z    |       30 |       31 |       32 |       33 |
+------+----------+----------+----------+----------+

And I need to get an output:

Name    Subject     Mark
-------------------------------
x   Subject1    10
x   Subject2    11
x   Subject3    12
x   Subject4    13
y   Subject1    20
y   Subject2    21
y   Subject3    22
y   Subject4    23
z   Subject1    30
z   Subject2    31
z   Subject3    32
z   Subject4    33

I need a dynamic query,because the number & name of the column keep changing from table to table. There are min 56 columns in each table. Likewise, i have 5 tables.

Your question shows no effort to solve it; -1 – OzrenTkalcecKrznaric Jul 22 '13 at 14:54 — OzrenTkalcecKrznaric, Jul 22 '13 at 14:54

score 1 · Answer 1 · answered Jul 22 '13 at 14:46

This process of converting multiple columns into multiple rows is known as an unpivot. If you have a known values, then you can use the following:

select name, subject, mark
from yourtable 
unpivot
(
  mark
  for subject in (Subject1, Subject2, Subject3, Subject4)
) unpiv;

See SQL Fiddle with Demo.

If you need to perform this dynamically, then you will use dynamic SQL:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'yourtable' and
                 C.column_name <> 'Name'
           for xml path('')), 1, 1, '')

set @query 
  = 'select name, subject, mark
     from yourtable
     unpivot
     (
        mark
        for subject in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

See SQL Fiddle with Demo.

Note: when you are unpivoting columns of data then the datatypes are required to be the same, so conversion of the data might be required.

Joao Leal · Answer 2 · 2013-07-22T14:55:11.737

It should be something like this (comments on the code). (Unpivot function documentation)

--Initialize variables
DECLARE @Columns VARCHAR(MAX) = ''
DECLARE @UnpivotColumns VARCHAR(MAX) = ''

SELECT @Columns = ',' + @Columns FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'your table'
SELECT @UnpivotColumns = ',' + @UnpivotColumns FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'your table' AND COLUMN_NAME LIKE 'Subject%'

--Remove first comma
SET @Columns = STUFF(@Columns,1,1,'')
SET @UnpivotColumns = STUFF(@UnpivotColumns,1,1,'')

--Assemble the dynamic query       
DECLARE @qry VARCHAR(Max) = 'SELECT Name, Subject, Mark
FROM 
   (SELECT ' + @Columns + '
   FROM pvt) p
UNPIVOT
   (Mark FOR Subject IN 
      (' + @UnpivotColumns + ')
)AS unpvt;
GO'

--PRINT(@qry)
EXEC (@qry)

Variation of pivot in SQL

2 Answers2