SQL - Convert column to rows including column name

Question

I have a table like this :

date    subj1   subj2   subj3   subj4
1       20       5      30      7
2       15      14      29      4
3       15      14      29      14

I want to get it arranged like the following:

date    1   2   3
subj1   20  15  35
subj2   5   14  14
subj3   30  29  29
subj4   7   4   14

How can I achieve this in SQL using pivot or unpivot ?

Answer 1

using cross apply() with values() to unpivot your data in a common table expression, then pivoting it with pivot():

with cte as (
  select t.date, v.subject, v.value
  from t
  cross apply (values ('subj1',subj1),('subj2',subj2),('subj3',subj3),('subj4',subj4)) v(subject,value)
)

select subject, [1],[2],[3]
from cte
pivot (max(value) for [date] in ([1],[2],[3])) p

rextester demo: http://rextester.com/QJMRBF98845

returns:

+---------+----+----+----+
| subject | 1  | 2  | 3  |
+---------+----+----+----+
| subj1   | 20 | 15 | 15 |
| subj2   |  5 | 14 | 14 |
| subj3   | 30 | 29 | 29 |
| subj4   |  7 |  4 | 14 |
+---------+----+----+----+

---

If you want subject to be called date, then simply alias it in the select:

with cte as (
  select t.date, v.subject, v.value
  from t
  cross apply (values ('subj1',subj1),('subj2',subj2),('subj3',subj3),('subj4',subj4)) v(subject,value)
)

select subject as date, [1],[2],[3]
from cte
pivot (max(value) for [date] in ([1],[2],[3])) p

rextester demo: http://rextester.com/XQAE51432

returns:

+-------+----+----+----+
| date  | 1  | 2  | 3  |
+-------+----+----+----+
| subj1 | 20 | 15 | 15 |
| subj2 |  5 | 14 | 14 |
| subj3 | 30 | 29 | 29 |
| subj4 |  7 |  4 | 14 |
+-------+----+----+----+