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 ?

@TabAlleman The solution for the above question does not work for multiple columns — Rashid, Apr 18 '17 at 12:58

score 2 · Accepted Answer · answered Apr 18 '17 at 12:32

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 |
+-------+----+----+----+

The Query you made is static, if we add new row in table, this wont work buddy. :) make something dynamic ;) — Dheeraj Sharma, Apr 18 '17 at 14:28

score 0 · Answer 2 · answered Apr 18 '17 at 12:32

using PIVOT

declare @table table (data int ,subj1 int ,subj2 int,subj3 int, subj4 int )

insert into  @table 
select 1      , 20     ,  5     , 30  ,    7
union all select 2      , 15     , 14     , 29  ,    4
union all select 3      , 15     , 14     , 29  ,    14

select 'subj1' as [date],* from (
select data,subj1  from
@table) a
pivot ( sum(subj1) for data in ([1],[2],[3]))p

union all
select 'subj2' as [date],* from (
select data,subj2  from
@table) a
pivot ( sum(subj2) for data in ([1],[2],[3]))p

union all

select 'subj3' as [date],* from (
select data,subj3  from
@table) a
pivot ( sum(subj3) for data in ([1],[2],[3]))p
union all   
select 'subj4' as [date],* from (
select data,subj4  from
@table) a
pivot ( sum(subj4) for data in ([1],[2],[3]))p

RESULT

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

score 0 · Answer 3 · answered Apr 18 '17 at 12:36

CREATE TABLE #TABLE12
    ([DATE] INT, [SUBJ1] INT, [SUBJ2] INT, [SUBJ3] INT, [SUBJ4] INT)
;

INSERT INTO #TABLE12
    ([DATE], [SUBJ1], [SUBJ2], [SUBJ3], [SUBJ4])
VALUES
    (1, 20, 5, 30, 7),
    (2, 15, 14, 29, 4),
    (3, 15, 14, 29, 14)

 SELECT SUBJECT
    ,MAX([1])[1]
    ,MAX([2])[2]
    ,MAX([3])[3]
FROM(SELECT *
FROM #TABLE12
CROSS APPLY (VALUES ('SUBJ1', SUBJ1),
                    ('SUBJ2', SUBJ2),
                    ('SUBJ3', SUBJ3),
                    ('SUBJ4', SUBJ4)
                    ) 
            CROSSAPPLIED ([SUBJECT], VALUE))A
PIVOT(MAX(VALUE) FOR [DATE] IN (
            [1]
            ,[2]
            ,[3]
            )) P
            GROUP BY SUBJECT

output

SUBJECT 1   2   3
SUBJ1   20  15  15
SUBJ2   5   14  14
SUBJ3   30  29  29
SUBJ4   7   4   14

SQL - Convert column to rows including column name

3 Answers3